How to Count Colored Cells in Excel (4 Simple Ways)

In this dataset, there are three categories: Fruit, Flower, and Food, each marked with a different color. Let’s count the cells with a specific color, as shown in the GIF.

Method 1 – Using the Find & Select Command

• Select the data range with colored cells.
• Go to the Home tab, click on the Find & Select drop-down, and choose Find.
• A Find and Replace dialog box will pop up.
• Click Options.
• Click on the Format drop-down and go to Choose Format From Cell.
• A four-dimensional plus symbol will appear.
• Place the plus symbol over any colored cell and click on it. We picked the color Blue.
  
• The Preview label box will be filled with a color of the cell that you picked earlier.
• Select Find All.
  
• You will get all the details of the specified colored cells, along with the count of those colored cells.

Method 2 – Using the SUBTOTAL Function and the Filter Tool

• Select a blank cell below the data range.
• Apply the formula: =SUBTOTAL(102,B5:B16)

Here, the first argument set to 102 counts only the visible cells (hidden rows are excluded) in the given range. You will get the total count of the cells in the range.

• Select only the headers of the data range.
• Go to the Data tab and select Filter.
  
• This will insert a drop-down button in each header of the dataset.
• Click the drop-down button in the header of the column with colored cells.
• Choose “Filter by Color” from the drop-down list to see all colors from your data range in a sub-list.
• Click on the color you want to count.
  
• Excel will display only cells with the chosen color and show the count in the SUBTOTAL result cell.

• You can count all the other colored cells in your worksheet in Excel.

Method 3 – Applying GET.CELL Macro 4 and COUNTIFS Function

Step 1 – Create a Name Range

• Go to Formulas tab and the Define Names group, then select Define Name.
  
• In the New Name pop-up box, use the following:
  • Name: GetColorCode (this is a user-defined name)
  • Scope: Workbook
  • Refers to: =GET.CELL(38,GetCell!$B5)
    Here, 38 means the color of the referenced cell. GetCell means the sheet name that has your dataset. $B5 is the reference of the column with the background color.
  • Click OK.

  

Step 2 – Find the Color Code for Each Cell

• In a cell adjacent to the data, write the user-defined formula:=GetColorCode
• Press Enter.
  
• The formula will return a specific number specified in color.
• Drag the cell down with the Fill Handle.
  
• All the cells with the same background color will get the same number, and if there is no background color, the formula will return 0.

Step 3 – Apply the COUNTIFS Function

• Select the cell where you want to see the count of colored cells.
• Apply the formula:=COUNTIFS($E5:$E$16,GetColorCode)
  Here, $E5:$E$16 is the range of the color code that we extracted from the user-defined formula.
• You will get the count of the color-defined cells.
• Click on the next cell.
• Enter the following formula: =COUNTIFS($E5:$E$16,GetColorCode)

Method 4 – Using Excel VBA Code

• Go to the Developer tab and select Visual Basic to open the Visual Basic Editor. Alternatively, press Alt + F11.
• Navigate to the Insert tab and select Module to create a new module for writing your code.
  
• Copy and paste the following code:

  Function Count_Colored_Cells(ColorCells As Range, DataRange As Range)
  Dim Data_Range As Range
  Dim Cell_Color As Long
  Cell_Color = ColorCells.Interior.ColorIndex
  For Each Data_Range In DataRange
  If Data_Range.Interior.ColorIndex = Cell_Color Then
  Count_Colored_Cells = Count_Colored_Cells + 1
  End If
  Next Data_Range
  End Function

  This is not a Sub Procedure for the VBA program to run; this is creating a User Defined Function (UDF). So, after writing the code, don’t click the Run button from the menu bar.

• Go back to the dataset and define cells with colors as we did in the previous method.
• Use the following formula:=Count_Colored_Cells(E5,$B$5:$B$16)
  Here, Count_Colored_Cells is the user-defined function that you created in the VBA code. E5 is the color-defined cell and $B5:$B$16 is the range of the dataset with colored cells.
• Press Enter.
  You can see the count of colored cells.
• Drag down the cell using the Fill Handle tool.
  

Download the Practice Worksheet

Frequently Asked Questions

Is Excel VBA coding necessary to count colored cells?

No, VBA coding is not necessary, but it offers advanced customization options. Users with basic Excel skills can effectively count colored cells using simpler methods like formulas and filters.

Can I count colored cells in a specific range?

Yes, you can count colored cells in a specific range by adjusting the range parameter in your counting formula or method. This allows you to focus on a particular subset of your data.

Are there any limitations to counting colored cells in Excel?

While Excel provides various methods to count colored cells, these methods may have some limitations, such as compatibility issues or the need for adjustments when dealing with large datasets. It’s essential to choose a method that suits your specific requirements and constraints.

Count Colored Cells in Excel: Knowledge Hub

<< Go Back to Count Cells | Formula List | Learn Excel