How to Count Cells by Color in Excel

How to Count Colored Cells in Excel?

In Excel, you can count cells with colors applied either manually or through conditional formatting. Since this cannot be done directly with standard features, this article also explains how to count specific colored cells using VBA.

Method 1: Counting Colored Cells Using the Filter Function

By using Excel’s filter function, you can filter and display colored cells to count them.

Steps

1. Select the range of cells containing colors (e.g., A2:A20).
2. Click on the “Data” tab and enable the filter function by clicking “Filter”.
3. Open the filter menu, select “Filter by Color”, and specify the color you want to count.
4. Check the number of displayed cells.

Note

When counting using the filter function, you can visually confirm the specified color cells, but you need to manually count the displayed rows as they are not automatically calculated.

Method 2: Counting Specific Colored Cells Using VBA Macros

To count only specific colored cells, you can easily obtain the results using a VBA macro.

VBA Code

1. Click on the “Developer” tab and open the VBA editor by clicking “Visual Basic”.
2. Select “Insert” → “Module” and paste the following code:

VBA Code:

Function CountColorCells(rng As Range, colorCell As Range) As Long
    Dim cell As Range
    Dim count As Long
    count = 0
    
    For Each cell In rng
        If cell.Interior.Color = colorCell.Interior.Color Then
            count = count + 1
        End If
    Next cell
    
    CountColorCells = count
End Function

Explanation of the VBA Code

• rng As Range: Specifies the range of cells to count.
• colorCell As Range: Specifies the cell with the color to count.
• CountColorCells = count: Returns the number of cells in the range that match the specified color.

How to Use

1. Close the VBA editor and return to the Excel sheet.
2. If the range A2:A20 contains colored cells, enter the following formula in a cell (e.g., B2) to display the count result.

=CountColorCells(A2:A20, A1)

Here, set the desired color in cell A1 (fill A1 with the target color), and only cells with that color will be counted.

Example

In the table below, column A contains colored cells, and column B shows the count results. Cell A1 is set with the target color.

The count of cells in A2 to A10 filled with the color of A1 will be displayed.

Result

• Cell B2 will display the number of cells in the range A2:A10 that match the color of A1. In this example, “3” will be displayed.