Count Cells By Color In Excel

Count cells in Excel enables users to count the number of cells based on color. Though there is no inbuilt option to count the number of colored cells in Excel, there are a number of methods we can use to count cells by color.

For example, consider the below table showing temperatures in 3 different countries. Now, assume that we have to count the cells which are green in color.

Step 1: Press the shortcut keys, Ctrl + F. The Find and Replace window appears. Press on the Options>> button.

The window will display more options. Click on the Format option.

Step 2: The window, Find Format appears. Select Choose Format from Cell… option.

Next, using the eye dropper tool, select the desired color. In this example, it is green.

As soon as we select the color, we can see the Preview option highlighting the same color.

Press Find All. The result will be displayed as highlighted in the below image.

Likewise, we can count number of colored cells in Excel.

• Count number of colored cells is possible in Excel and there are many methods to count colored cells in Excel.
• Though we do not have any built-in function in Excel, we can easily count the colored cells using the following methods
  • The Auto Filter option
  • The VBA code
  • The FIND method
  • The filter method with a subtotal function
• The FIND method is one of the easiest and simple methods to count the number of coloured cells in Excel.

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

1. Method #1 – Count cells with color using the filter method with a subtotal function.
2. Method #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.

#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 coding.

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 is the 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 Function,” 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.

• 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.

This article has been a guide to Count Cells By Color in Excel. We discuss counting 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: –

• Sum by Color in Excel
• Count Colored Excel Cells
• Sort by Color in Excel
• Alternate Row Color in Excel