Count or Sum cells based on color using Excel filter

 

Count or Sum cells based on color using Excel filter

Yes! We can use Excel filter to do this if we couple it with the appropriate formula. Your usual COUNT or SUM functions won’t help in this case. This is because Excel filter simply hides the result not fulfilling the criteria and both COUNT and SUM functions consider hidden and visible data therefore, we need a function that processes only visible data and ignore the hidden one.

This is where we meet SUBTOTAL function. This approach is especially good for those who want to avoid getting into VBA.

Step 1: For this approach we need the data to have headings. If data does not have headings already then its suggested to have heading inserted for each column at the top by adding a new row or at least for the column containing colored cells.

Step 2: Selected the cells containing headers and go to Data tab > in sort and filter group click Filter button and you will see drop-down arrows added to the heading cells.

Step 3: Move to the last cell of column containing colored cells. You can do that quickly by simply having an active cell within in that column and hitting CTRL+DownArrow. Move one cell down further and put the following formula for count:

=SUBTOTAL(2,B2:B10)

I had my data in range in cells B2 through B10 only that is why formula contains just that.

Step 4: Click the drop-down arrow button of the column containing colored. A menu will appear and in there hover the cursor over filter by color option and select the desired. This will filter the data down to those cells that contain the selected color only.

If you want to sum the values in specific colored cells you need to change the formula a little as following:

=SUBTOTAL(9,B2:B10)

However, this approach is quite limited. As you can process only on one color at a time. If you have more than one color in large data set, and you want to count, sum or average specifically colored data then much better and faster approach is by making a simple UDF in VBA.

Count cells based on color using VBA

For this we need a UDF i.e. user defined function. Following are the steps:

Step 1: Hit ALT+F11 shortcut key to enter visual basic environment

Step 2: Once you are inside visual basic editor go to Insert > module to insert a new module.

Step 3: Double click newly created module and a new blank window will open on the right. Copy the following code and paste it inside empty window:

Function CountColor(color As Range, data_range As Range)
 Dim dRange As Range
 Dim dColor As Long
dColor = color.Interior.ColorIndex
For Each dRange In data_range
 If dRange.Interior.ColorIndex = dColor Then
 CountColor = CountColor + 1
 End If
Next dRange
End Function

Step 4: The above code has now given you a new function to use named CountColor. Just provide the cell address containing the color you want to use as count criteria and then provide the range of data to process.

Following illustration will walk you through the process:

And you can count for multiple colors at once as well:

Understanding VBA code

Lets understand what the code is about and what are we achieving with it.

Line 1:
Function CountColor(color As Range, data_range As Range)

Keyword “Function” tells Excel that we want to initiate UDF with the name “CountColor”. Inside bracket we required two arguments; “color” and “data_range” and both are of type range meaning we have to mention either a cell address or a range of cells. In descriptive manner the formula is following:

CountColor(cell address with the background color you want to use as criteria, range of cells you want to check for color selected and count)

Remember we have defined two variables color and data_range. But we need to tell from where to get the values for these two and what to do with it. So far it is just a disclosure.

Line 2 & 3:
Dim dRange As Range
Dim dColor As Long

Two variables with the name dRange and dColor are defined. These two will be used to process the information given as part of function arguments mentioned inside parenthesis in line 1. I will explain it further in a bit.

Line 4:
dColor = color.Interior.ColorIndex

Remember dColor variable defined in line 2 and 3? and “color” variable mentioned in line 1?

Once user has mentioned the cell address for first argument “color” we want Excel to get the color index or simply the specific color number and store it as a value of variable dColor. Once stored we can use this color index number as a criteria.

Line 5 to 8:
For Each dRange In data_range
If dRange.Interior.ColorIndex = dColor Then
CountColor = CountColor + 1
End If

Remember we defined dRange as variable and has data_range as argument in line? In fifth line we asked Excel to take each cell in range (that user has mentioned for second argument as data_range) and equate it as dRange.

In sixth line we are invoking IF condition statement and we are checking if the color index number of any cell is equal to the color index number of dColor variable (remember we stored a specific color index number in line 4 above) then add “1” to the count. 

Terminate IF statement once all cells are checked going through each element in dRange.

This essentially creates a loop and check each cell mentioned in range and once all cells are checked loop is terminated.

In few words this code is fetching the color index number of cell mentioned by user and then comparing it against the color index numbers of each and every cell in the range specified. The ones that match are counted in and the ones that doesn’t match are ignored. That is how we get the count.

Sum cells based on color using VBA

Now that we understand how code works. We can modify it just a tad bit to have another function that sums the value of cells that fulfill criteria. Have a look at the following code that makes a new UDF that sum values based on cell color:

Function SumColor(color As Range, data_range As Range)
 Dim dRange As Range
 Dim dColor As Long
dColor = color.Interior.ColorIndex
For Each dRange In data_range
 If dRange.Interior.ColorIndex = dColor Then
 SumColor = SumColor + dRange.Value
 End If
Next dRange
End Function

It is exactly the same code as we had for count function with only difference. Instead of adding “1” for each cell matching the criteria, we want excel to take the value of that cell and add it up.

As simple as it can get!

Here is the illustration that shows count and sum of cell values based on color: