Count cells based on color

You can count cells based on color using a custom formula in Excel. In the example, we’ll show you how to do that!

Counting colored cells in Excel can be done using different methods depending on your needs. Since Excel doesn’t have a native function to count cells by color directly, we’ll use a custom function as a workaround. I will show you a flexible function that will allow you to get the desired result as easily and quickly as possible. We use a UDF to access the Excel Object Model (which is responsible for handling colors).

How to count cells based on cell color

To count colored cells in Excel, follow these steps:

1. Type =COUNTBYCOLOR(B2, E5:E10)
2. Add the cell (B2) that contains the color you want to count.
3. Select the range (E5:E10) where you want to count the cells with that color.
4. Press Enter to see the result.

COUNTBYCOLOR Function Example

The main goal of the COUNTBYCOLOR function is to count cells in a range that matches a specific color. It requires only two arguments. The first is the cell with the color to be matched. The second is the range to check. To make it clear, here’s a practical example.

In the example, the formula =COUNTBYCOLOR(B4, D2:D11) is used to count the number of cells in the range D2:D11 that have the same color as cell B4. The result is 3, indicating three cells with the same color as B4 in the specified range.

Under the Hood

In this section, we publish the function’s source code. We have added comments to make it easier to understand. For those interested in how the function works, this is likely the most effective way to help.

Function COUNTBYCOLOR(refColor As Range, ParamArray cellRanges() As Variant) As Long
    Dim currentCell As Range           ' Variable to iterate through each cell in the target range
    Dim singleRange As Variant         ' Variable to hold each range in the ParamArray
    Dim totalMatches As Long           ' Variable to store the total count of matching cells
    Dim matchCriteria As Integer       ' Variable to determine the type of cells to count (all, non-blank, blank)
    
    totalMatches = 0                   ' Initialize the total matches count to 0
    matchCriteria = 0                  ' Default to counting all cells
    
    ' Check if the last argument in the ParamArray is a number, which would set the match criteria
    If UBound(cellRanges) > 0 Then
        If IsNumeric(cellRanges(UBound(cellRanges))) Then
            matchCriteria = cellRanges(UBound(cellRanges))
        End If
    End If
    
    ' Loop through each range provided in the ParamArray
    For Each singleRange In cellRanges
        ' Check if the current item in ParamArray is a Range
        If TypeName(singleRange) = "Range" Then
            ' Loop through each cell in the current range
            For Each currentCell In singleRange
                ' Check if the cell color matches the reference color
                If currentCell.Interior.color = refColor.Interior.color Then
                    ' Decide what to count based on the match criteria
                    Select Case matchCriteria
                        Case 0 ' Count all cells (default)
                            totalMatches = totalMatches + 1
                        Case 1 ' Count only non-blank cells
                            If Not IsEmpty(currentCell.Value) Then
                                totalMatches = totalMatches + 1
                            End If
                        Case 2 ' Count only blank cells
                            If IsEmpty(currentCell.Value) Then
                                totalMatches = totalMatches + 1
                            End If
                    End Select
                End If
            Next currentCell
        ' If the item is an array of ranges (from a multi-area range), loop through each sub-range
        ElseIf TypeName(singleRange) = "Variant()" Then
            Dim subRange As Range       ' Variable to hold each sub-range within a multi-area range
            For Each subRange In singleRange
                ' Loop through each cell in the sub-range
                If TypeName(subRange) = "Range" Then
                    For Each currentCell In subRange
                        ' Check if the cell color matches the reference color
                        If currentCell.Interior.color = refColor.Interior.color Then
                            ' Decide what to count based on the match criteria
                            Select Case matchCriteria
                                Case 0 ' Count all cells (default)
                                    totalMatches = totalMatches + 1
                                Case 1 ' Count only non-blank cells
                                    If Not IsEmpty(currentCell.Value) Then
                                        totalMatches = totalMatches + 1
                                    End If
                                Case 2 ' Count only blank cells
                                    If IsEmpty(currentCell.Value) Then
                                        totalMatches = totalMatches + 1
                                    End If
                            End Select
                        End If
                    Next currentCell
                End If
            Next subRange
        End If
    Next singleRange
    
    COUNTBYCOLOR = totalMatches   ' Return the total count of matching cells
End Function

You can download the sample file here if you do not want to install our add-in.

Final words

It is evident that in many cases, despite our best intentions, we can’t use built-in Excel formulas. In such situations, it’s worth writing a simple function using VBA. You can easily use the function you create in any Excel environment without compatibility issues.

Additional resources:

• How to add leading zeros in Excel?
• IF statement based on cell color