Count Colored Cells in Excel

Top 3 Methods to Count Colored Cells In Excel

There is no built-in function to count colored cells in excel, but below mentioned are three different methods to do this task.

  1. Count Colored Cells By Using Auto Filter Option
  2. Count Colored Cells by using VBA Code
  3. Count Colored Cells by Using FIND Method

Now, let us discuss each of them in detail –

#1 – Excel Count Colored Cells By Using Auto Filter Option

For this example, look at the below data.

Count colored cells excel example 1

As we can see, each city is marked with different colors. So we need to count the number of cities based on cell color. Follow the below steps to count cells by color.

Follow the below steps to count cells by color.

  1. Apply the filter to the data.


    Count colored cells excel example 1-1

  2. At the bottom of the data, apply the SUBTOTAL function in excel to count cells.


    Count colored cells excel example 1-2

  3. SUBTOTAL function contains many formulas in it; this is helpful if we want to count, sum, average only visible cells data. Now under the heading “PIN”, click on the drop-down list filter and select Choose by Color.


    Count colored cells excel example 1-3

  4. As we can see, all the colors in the data. Now choose the color that you want to filter.


    Count colored cells excel example 1-4
    Wow!!! As we can see in the cell D21, our SUBTOTAL function is given the count of filtered cells as 6, instead of the previous result of 18.

Similarly, now choose other colors to get the count of the same.

example 1-5

So blue colored cells count five now.

#2 – Excel Count Colored Cells by using VBA Code

VBA’s street smart techniques help us to reduce time consumption at our workplace for some of the complicated issues.

Not only can we reduce time, but we can also create our own functions to fit our needs. One such function we can create a function to count cells based on color. Below is the VBA codeVBA CodeVBA 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 to create a function to count cells based on color.

Code:

Function Color_Cell_Count(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
Color_Cell_Count = Color_Cell_Count + 1
End If

Next Data_Range

End Function

Copy and paste the above code to your module.

Count Cells using VBA Code 1

This code is not a SUB Procedure to run; rather, it is a “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” (UDF).

The first line of the code “Color_Cell_Count” is the function name. Now create three cells and color them as below.

Count Cells using VBA Code 1-1

Now open the function “Color_Cell_Count” in G2 cell.

Count Cells using VBA Code 1-2

Even though we don’t see the syntax of this function, the first argument is what color we need to count, so select the cell F2.

Count Cells using VBA Code 1-3

The second argument is to select the range of cells as D2:D19.

Count Cells using VBA Code 1-4

Close the bracket and hit the enter key. This will give the count of cells that has the selected cell color.

Count Cells using VBA Code 1-5

Like this, with the help of UDF in VBA, we can count cells based on cell color.

#3 – Excel Count Colored Cells by Using FIND Method

We can also count cells based on the FIND method as well.

  • Step 1: First, select the range of cells where we need to count cells.
Count Cells by Use FIND Method 1
  • Step 2: Now press Ctrl + F to open the FIND dialogue box.
Count Cells by Use FIND Method 1-1
  • Step 3: Now, click on “Options>>.”
Count Cells by Use FIND Method 1-2
  • Step 4: It will expand the FIND dialogue box. Now click on the “Format” option.
Count Cells by Use FIND Method 1-3
  • Step 5: Now, it will open up the “Find Format” dialogue box. Click on the “Choose Format From Cell” option.
Using FIND Method 1-4
Using FIND Method 1-5
  • Step 7: Select the cell which is formatted as the desired cell count. I have chosen the F2 cell as the desired cell format, and now I can see the preview.
Using FIND Method 1-6
  • Step 8: Now click on FIND ALL option to get the count of the selected cell format count of cells.
Using FIND Method 1-7

So, a total of 6 cells were found with selected formatting colors.

Things to Remember

This has been a guide to Count Colored Cells in Excel. Here we learn count colored cells by using auto filter option, VBA code, and FIND method and downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>