How to Count Cells with Color in Excel?

There are multiple ways we can count cells based on the color of the cell in Excel.

  • Method #1 – Count cells with color using the filter method with a subtotal functionMethod #2 – Count cells with color by creating a function using VBA code.

Now, let us discuss each of the methods in detail, along with an example –

#1 Using Filter Method with Sub Total Function

As we said, we have multiple methods to count cells based on the color of the cell in Excel. This method uses the filter and subtotal function.

This count is 2, so we have an updated result.

  • First, we need to apply a subtotal function, so open the SUBTOTAL function in Excel at the end of the data. Once we open the SUBTOTAL function, we are supplied with a function list under this; since we are counting cells, choose the 2 – COUNT function. After selecting the subtotal method next, we need to choose a range of cells to be counted. Now the count of these cells will be 7. For the above data, the structure applies a filter option. We must click on the drop-down list of filters to go to “Filter by Color.” It will show up all the available colors to filter. We must choose any color. As a result, we will have those colored rows. As we can see above, our SUBTOTAL function shows the count of the filtered rows only. So, for example, since there are only two rows marked with orange color, the SUBTOTAL function shows the count as three only. Similarly, choose another color and see whether it works perfectly or not.

#2 Create Function using VBA Coding

Unfortunately, as we told you, we do not have any built-in function which can help us count cells based on color in Excel. Again, we have been told there is no built-in function, but the beauty is we can create our function by using VBA codingUsing VBA CodingVBA code refers to a set of instructions written by the user in the Visual Basic Applications programming language on a Visual Basic Editor (VBE) to perform a specific task.read more.

Below is the code we have already written to create a function to count cells based on color.

Code:

Function ColorCount(ColorCell As Range, DataRange As Range)

Dim Data_Range As Range Dim Cell_Color As Long

Cell_Color = ColorCell.Interior.ColorIndex

For Each Data_Range In DataRange

If Data_Range.Interior.ColorIndex = Cell_Color Then ColorCount = ColorCount + 1 End If

Next Data_Range

End Function

As shown below, we have to copy the above code and paste it into our VBA module.

Now come back to the worksheet where we have data and create three cells with color.

We must put an equal sign and open the function we have created using the above code.

The first argument with this function is “we need to select the color cell that we need to count,” so in this case, we will choose the E2 cell.

The second argument in which range of cells we need to count the selected cell color.

That is all we need for this function. Next, close the bracket and press the “Enter” key to get the count of the selected cell color.

Similarly, we need to do it for other colored cells. Again, we get the following result.

It is done by using the VBA “User Defined FunctionUser Defined FunctionUser Defined Function in VBA is a group of customized commands created to give out a certain result. It is a flexibility given to a user to design functions similar to those already provided in Excel.read more,” which will loop through a provided range of cells and count the number of cells that exactly hold the same color as the provided cell color.

Then it will sum all the matched color cells and return the result as a count of that color cell.

Like this, we can count cells with colors in Excel. There are other methods, but those are slightly complicated, so we must go for the easy one at the start of the learning.

Things to Remember here

  • The SUBTOTAL function counts cells only, which are visible, so as the filter is applied, it will count only those filtered colored cells.VBA code is for the UDF function because there are no built-in functions, so we have to create our own.
  • Sum by Color in ExcelCount Colored Excel CellsSort by Color in ExcelAlternate Row Color in Excel