How to Count Colored Cells in Excel

While not a built-in Excel feature, we can create our own function to easily count colored cells.

Contents:

1. How to Use VBA User Defined Functions

2. How to Count All Colored Cells

3. How to Count Cells Matching Cell Color

How to Use User Defined Functions

The two examples below will use a "User Defined Function". Just like how SUM and COUNT are considered functions, we can create our own functions using VBA.

This is the same process as using a macro, but instead of running a macro, we can instead call our function similar to how the built in functions are called in a formula (by typing "=" followed by the function name).

The first step to using a user defined function is to take the snippet of VBA code, and place that into your workbook. To do this, open up the VBA editor by pressing alt + F11 or going to the developer tab > Visual Basic:

Next, we'll need to create a module to place the custom functions in. On the left menu, right click on your workbook name, and select insert > Module. Any code entered in to this module will be available for use anywhere in the workbook.

Then all you have to do is drop the Function code into the blank area to the right of that menu, and you'll be able to call in your custom function by its name in any cell in your workbook.

How to Count All Colored Cells in a Range

To count all colored cells in a range, or any cell in a given range that does not have a blank background, we can use a user defined function we'll call "CountColoredCells".

This function will search a given range for any colored cells and count them up.

Function CountColoredCells(eval_range As Range) As Long
    Dim cell As Range
    Dim colored_count As Long

    colored_count = 0

    For Each cell In eval_range
        If cell.Interior.ColorIndex <> xlNone Then
            colored_count = colored_count + 1
        End If
    Next cell

    CountColoredCells = colored_count
    
End Function

For example, here we have a workbook with a few different colors denoting different results. If we want to know how many cells in this range are colored, we can use the formula (after we've dropped the function into a module):

= CountColoredCells(cell_range)

= CountColoredCells(C4:C10)

By using the "CountColoredCells" function and selecting the range we want to count, we can return the correct number of colored cells using our custom function.

How to Count Cells in a Range Matching a Specific Color

Instead of counting all colored cells in a range, what about counting cells in a range that match a specific color?

For example, we can use the same example above, but add in a green "Good". Using only the colors, we can count how many cells match each status.

Function CountColorMatches(eval_range As Range, cell_reference As Range) As Long
    Dim cell As Range
    Dim reference_color As Long
    Dim match_count As Long

    reference_color = cell_reference.Interior.Color
    match_count = 0

    For Each cell In eval_range
        If cell.Interior.Color = reference_color Then
            match_count = match_count + 1
        End If
    Next cell

    CountColorMatches = match_count
    
End Function

To use this function, we will be using a slightly different structure, by defining the range we want to look at as well as a cell containing the color that we want to count.

= CountColorMatches(cell_range, reference_cell)

= CountColorMatches($C$4:$C$10,E4)

Here our function is looking at the color of E4, and counting the number of times that cell color occurs in the range C4:C10. By dragging that same formula down, we can look at each color, green, yellow and red.

To illustrate that this is counting the colors and not text, we can remove the text and the count will stay the same.