# Count Cells By Color in Excel Article byJeevan A Y ## 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.

1. Method #1 – Count Cells With Color Using Filter Method with Sub Total Function
2. Method #2 – Count Cells with Color By Creating 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 I said, we have multiple methods to count cells based on the color of the cell in excel. This method uses the Filter & Subtotal function.

Steps to use the Filter method with subtotal function in excel are as follows.

1. First, we need to apply a subtotal function, so at the end of the data, open the . 2. Once we open the SUBTOTAL function, we are supplied with a function list under this; since we are counting cells, choose the u201c2 u2013 COUNTu201d function. 3. After selecting the subtotal method next, we need to choose a range of cells to be counted. 4. OK, now the count of these cells will 7. 5. For the above data, the structure applies a filter option. 6. Click on the drop-down list of filters to go to u201cFilter by Color.u201d It will show up all the available colors to filter. 7. Choose any of the colors, and we will have those colored rows. 8. As youu00a0canu00a0seeu00a0above, our SUBTOTAL function is showing the count of the filtered rows only. Since there are only two rows marked with Orange color, the SUBTOTAL function showing the count as three only; similarly, choose another color and see whether it is working perfectly or not. This count is 2, so we have an updated result.

### #2 Create Function using VBA Coding

As I told you, unfortunately, we don’t have any built-in function, which can help us to count cells based on color in excel. We have told there is no built-in function, but the beauty is we can create our own function by .

Below is the code I 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```

All that you have to do is to copy the above code and paste it in your VBA module, as shown below.

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

Put equal sign and open the function that we have created by 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, I will choose the E2 cell.

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

OK, that’s all we need for this function. Close the bracket and hit the enter key to get the count of the selected cell color.

Similarly, do for other colored cells. We get the following result.

This is done by using VBA “,” it will loop through a provided range of cells and count the number cells which are exactly holding the same color as the provided cell color.

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

Like this, we can count cells with colors in excel. There are other methods as well, but those are slightly complicated, so 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 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.

This has been a guide to count cells with color in excel. Here we discuss how to count cells with color by using a filter option with subtotal function and VBA code in excel and a downloadable template. You may learn more about excel from the following articles –

• 35+ Courses
• 120+ Hours