Count colored cells in Excel

Count the meaning behind the color first

If green means Complete, store that status in a column and count the status. This keeps your calculation independent of formatting. With status values in B2:B10, use:

=COUNTIFS(B2:B10,"Complete")

Use the following methods when manually applied fill color is your only source of information.

Count without a macro

1. Select your table, including its headers, and choose Data → Filter.
2. Open the dropdown in the colored column and choose Filter by Color.
3. Enter the following formula outside the filtered range. Point it at an ID column populated on every row.

=SUBTOTAL(103,A2:A10)

Function number 103 counts visible, nonempty cells and ignores manually hidden rows too. Blank IDs will not count. A formula returning an empty string still counts as nonempty, so use actual IDs.

Add the CountColor function

1. In Windows, press Alt + F11. On Mac, open Tools → Macro → Visual Basic Editor.
2. Choose Insert → Module and paste the code below into the standard module.
3. Save the workbook as .xlsm. Enable macros only when you understand and trust the file.

Option Explicit

Public Function CountColor(ByVal cells As Range, _
                           ByVal sample As Range) As Long
    Application.Volatile
    Dim cell As Range
    Dim count As Long
    For Each cell In cells.Cells
        If cell.Interior.Color = sample.Cells(1, 1).Interior.Color Then
            count = count + 1
        End If
    Next cell
    CountColor = count
End Function

Use it in a worksheet

Apply manual fills to B2:B10, and give E2 the fill you want to count. Enter:

=CountColor(B2:B10,E2)

This counts matching fills even when a cell is empty. It includes hidden rows. Use a bounded range instead of an entire column to avoid unnecessary work.

Conditional formatting is different

Interior.Color reads a directly applied fill, not the color produced by a conditional formatting rule. DisplayFormat cannot be used by a worksheet UDF, so swapping that property into this code is not a solution. Reproduce the formatting condition with COUNTIFS instead.

Download the example

A VBA source module, not a workbook with embedded macros. Read the code and follow the setup steps above.