Like and see, form a habit; The way of civil and military affairs, one by one.
Wechat search [Yixin Excel] to pay attention to this different we-media person.
In this paper, making github.com/hugogoos/Ex… Has been included, including Excel system learning guide series of articles, as well as a variety of Excel materials.
I don’t know if you’ve ever encountered the need to sum up a cell’s data based on its fill color. Today I’m going to share with you five tips for summing by color.
1. Search method
The search method mainly uses the color format to find out all the cells that meet the conditions, and then uses Excel itself to calculate the good results.
First press the shortcut key “Ctrl+F” to bring up the Find and replace dialog box.
Then click Options, then the Format drop – down list, and select Select Format from cells.
At this point, the mouse will turn into a white cross + straw shape and then absorb the color of our request and cell.
Then click “Find All” and you will find all the cells that meet the criteria at the bottom of the Find and replace dialog. Then press the shortcut key “Ctrl+A” to select all cells that meet the conditions, as shown in the picture below. Then you can see the sum of the values of all yellow cells on the taskbar.
Dynamic demonstration is as follows:
2. Find + define nomenclature
There are some flaws in the above search method: (1) The final need to manually enter the results; ② If the cell content changes and does not automatically change, you need to manually operate it once.
To address the shortcomings of the lookup method, we can use the lookup + definition nomenclature. The operation method is also very simple. After finding all yellow cells in the search method, press the shortcut key “Ctrl+F3” to bring up the name manager dialog box.
Click “New” to bring up the New name dialog box.
In the name field we can customize yellow and click OK to close the name Manager dialog.
Then enter the formula in the summation cell: “=SUM(yellow)”.
Take a look at the motion picture:
3. Screening method
In the screening method, cells are screened by color and then summed by function.
First select the data area, click “data” → “Filter”, and then click the filter inverted triangle button, select by color filter, select yellow.
Then enter the formula “=SUBTOTAL(109,B4:B10)” in the D6 cell.
=SUBTOTAL(109,B4:B10) =SUBTOTAL(109,B4:B10) =SUBTOTAL(109,B4:B10) 109 is the SUM of SUM, and it ignores the hidden value of the hidden row, and if it’s 9 it doesn’t ignore the hidden value of the hidden row and it’s counted, and the hidden row is always hidden manually, and the filtered rows are never counted, So in this example, “=SUBTOTAL(9,B4:B10)” or “=SUBTOTAL(109,B4:B10)” will give the same result.
The dynamic effect is as follows:
4. Macro table function method
Macrotable function method mainly uses GET.CELL to GET CELL background color value, and then uses function to calculate and.
The macro table function get. CELL was used in earlier versions of Excel to return information referencing cells. It can still be used in older worksheets, but not directly in cells, only with defined names.
First click “Formula” → “Define Name” to bring up the new name dialog box,
Custom input in name field: color; Reference the position input formula: “=GET.CELL(63, macrotable function method!A4)”. Parameter 63 represents the background color of the cell.
Then enter the formula “= color” in the D4 cell and fill the D4:F7 cell area.
Then enter the formula “=SUMIF(D4:F7,6,A4:C7)” in cell D9.
The function of the formula “=SUMIF(D4:F7,6,A4:C7)” is to calculate the sum of the corresponding A4:C7 cell region values of D4:F7 equal to 6.
Specific demonstration is as follows:
5, and the method of VBA
The power of VBA is believed to be known, so VBA can also be implemented by color summation. The detailed operations are as follows.
Click “Development tools” → “Visual Basic” to bring up the VBA editing window, and then right click “Microsoft Excel object” under the current worksheet, choose “Insert” → “module”.
Then enter the following code in the edit box:
Function SumColor(color As Range, sumRange As Range) As Double
Dim icell As Range
For Each icell In sumRange
If icell.Interior.ColorIndex = color.Interior.ColorIndex Then
SumColor = Application.Sum(icell) + SumColor
End If
Next icell
End Function
Copy the code
Click Save to close the VBA window.
Then input the formula in cell D9: “=SumColor(C9,B4:E7)”, where the first parameter C9 represents the color cell that requires and, and the second parameter B4:E7 represents the data area.
Let’s take a look at the process:
Today’s sharing ends here, but the road of learning has just begun, I hope we can keep moving forward on the road of learning, perseverance.
If you are interested in the feature, you can tell xiaobian oh, xiaobian will write a corresponding article for you. Of course, it’s first come, first written. I will make a schedule and try to meet everyone’s needs. So if the next article is not what you want, please don’t worry, it may be the next one. Let me know what you want to learn.
This article continues to update, you can search wechat “yixin Excel” first time to read, this article GitHub github.com/hugogoos/Ex… Excel System Learning Guide series, welcome Star.