Count Excel Cells by Color in Microsoft Excel

Excel has no built-in COUNTIF formula that counts cells by fill color. For a quick count, filter the range by color and count the visible rows; for a reusable worksheet formula, use a small VBA function, with the important limitation that changing a cell’s fill color does not automatically recalculate that function.

Why Excel does not count fill colors with COUNTIF

Most Excel formulas evaluate cell values: text, numbers, dates, logical values, or results returned by other formulas. Fill color is formatting, not a value. That separation is why a formula such as COUNTIF can count cells containing “Late” but cannot directly count cells that look red.

It also explains why two cells that look identical may behave differently. One might have a red fill applied manually, while another appears red because a conditional formatting rule changed its display. Excel stores and applies those kinds of formatting differently. A dependable method depends on whether you need a quick one-time answer, a count that updates with data, or a count based on the color currently displayed.

Before choosing a method, check what the color means. If red means “overdue” and the underlying date or status identifies overdue items, counting that data is usually more reliable than counting the red formatting. Color is often a visual signal for information that already exists in the worksheet.

Quick method: filter the range by color

For a one-time count of manually colored cells, Excel’s filter menu is usually the simplest option. It does not require macros, and it can filter a column by its cell fill color.

Make sure the data has a header row and that the color you want to count is in one column.
Select a cell in the data, then choose Data > Filter. Excel adds filter arrows to the headers.
Open the arrow for the colored column and choose Filter by Color, then select the fill color.
Count the visible matching records. For a reliable count, use a column that contains an entry on every data row.

To calculate that visible-row count in a cell, assume IDs are in A2:A20, every record has an ID, and the color filter is applied to the relevant rows. Enter =SUBTOTAL(103,A2:A20). If seven IDs remain visible after filtering, the result is 7. The 103 option counts nonblank cells while excluding rows hidden by the filter and rows hidden manually.

The result is a count of visible records, not a special formula that recognizes the color. Clear or change the filter and the number changes accordingly. If the colored cells are blank, count a different column with one nonblank value per record; otherwise, a nonblank-cell count will miss rows even when their colored cell is visible.

Use a helper column when color represents data

If people apply colors to communicate categories—such as “Approved,” “Needs review,” or “On hold”—store those categories as text in a separate column. Then use a normal formula to count the category. This is generally the best approach for reports that need to stay accurate as data changes, and it works without macros.

For example, assume statuses are in B2:B20 and the cells contain the word “Approved.” Enter =COUNTIF(B2:B20,"Approved") to count them. If there are five cells with that status, the formula returns 5. Apply conditional formatting to make those cells green if useful, but keep the status value as the source of truth.

A helper column is especially useful when color is currently the only record of a decision. Add a status field, fill it in for each row, and use that field for formulas, charts, and filters. This also makes the workbook easier to understand for someone who cannot distinguish the colors or views it in a different display environment.

Use text or a code such as “Review” for categories.
Use a date field to identify overdue items rather than relying on red fill.
Use a number or checkbox field for completion, then format completed rows visually.

Count manually applied colors with a VBA function

If the workbook genuinely needs to count manually applied fills with a worksheet formula, a VBA user-defined function can compare each cell’s fill with a sample cell. This is more convenient than filtering when you want a displayed result, but it requires a macro-enabled workbook and does not work in Excel for the web.

In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor, insert a standard module, and add this code. On Windows, Alt+F11 opens the editor; on Mac, use Excel’s Visual Basic Editor from the available macro or developer tools. The function below compares the fill pattern and color, so you can use a sample cell with the fill you want to count.

Function CountByFill(ByVal dataRange As Range, ByVal sampleCell As Range) As Long
    Dim cell As Range
    Dim targetColor As Long
    Dim targetPattern As Long

    ' Match both the fill pattern and its color.
    targetColor = sampleCell.Interior.Color
    targetPattern = sampleCell.Interior.Pattern

    For Each cell In dataRange.Cells
        If cell.Interior.Color = targetColor And _
           cell.Interior.Pattern = targetPattern Then
            CountByFill = CountByFill + 1
        End If
    Next cell
End Function

Assume colored cells are in B2:B20 and D2 is a sample cell filled with the color to count. Enter =CountByFill(B2:B20,D2) in a worksheet cell. If six cells in B2:B20 have the same fill pattern and color as D2, the result is 6. The sample cell can be outside the data range. Use a representative sample with a solid fill if the goal is to count a particular solid color.

Save the file as an Excel Macro-Enabled Workbook (.xlsm), and allow macros only when you trust the workbook and its source. Save a separate copy before running or relying on a macro: macro actions cannot be undone with Ctrl+Z. The code compares direct cell formatting; it is not a reliable way to count colors produced by conditional formatting.

Important limits: recalculation and conditional formatting

A fill-color change is a formatting change, not a normal change to a formula’s input value. As a result, the VBA count may remain unchanged after you recolor a cell. Re-entering the formula or forcing calculation may refresh the displayed result, but a formatting edit alone is not a dependable recalculation trigger. For a workbook used by others, explain this limitation or choose a data-based helper column instead.

Conditional formatting needs separate treatment. A cell can have one underlying fill while a rule makes it appear another color on screen. The VBA function above reads the cell’s direct Interior formatting, so it may not match the displayed conditional-format color. Excel’s DisplayFormat property can reflect displayed formatting in some VBA contexts, but it is not suitable for a worksheet user-defined function in the same way; using it inside a cell formula can cause problems. A safer solution is to count the condition that triggers the formatting rule.

For instance, if a rule turns a cell red when its status is “Late,” count the “Late” status values with COUNTIF. If the rule is based on a date, count dates that meet the same date condition. That approach follows the underlying logic, updates as values change, and avoids trying to infer data from appearance.

Choosing the right counting method

The right approach depends on why the cells are colored and how often the count must update. A filter is quick for an occasional check. A helper column is strongest for ongoing tracking and shared workbooks. VBA is an option when direct fill color is truly the data and the people using the file can work with macros.

One-time count: Filter by the desired fill color and count visible, nonblank IDs.
Recurring report: Store the category in a column and count it with a standard formula.
Manually filled cells in a desktop workbook: Use a VBA function, while accounting for macro security and recalculation limits.
Color from conditional formatting: Count the values or rule conditions that create the color.

When sharing a workbook, remember that recipients may have macros disabled, use Excel for the web, or rely on accessibility tools that do not convey color alone. A text label alongside the color makes the information clearer and the count easier to verify.

Common mistakes to avoid

One common mistake is expecting COUNTIF to accept a fill color as its criterion. The criterion in that function tests cell values; it does not inspect formatting. Another is filtering the colored column and counting its visible cells when some of those cells are blank. Count a complete ID or name column instead.

It is also easy to count the wrong kind of color. A manually applied fill and a conditional-format fill can look the same but require different approaches. If the color changes when a value changes, find and count the rule’s underlying condition. If it was painted on manually, a color filter or the VBA function may be appropriate.

Finally, don’t assume a color count is a permanent audit trail. Someone can recolor a cell without changing the underlying record, and a later user may not know what the color was meant to signify. A status value makes the meaning explicit and gives formulas something stable to evaluate.

Frequently Asked Questions

Can COUNTIF count cells by color?

No. COUNTIF evaluates cell contents, not fill color. Use a filter for a quick count, VBA for manually applied fills in desktop Excel, or count a value that represents the color’s meaning.

Can I count cells by color without VBA?

Yes. Filter the colored column using Filter by Color, then count visible nonblank cells in a column that has an entry for every row. A helper column with categories is the better formula-based option for a count that should update with the data.

Does the VBA formula count conditional-formatting colors?

The function shown above compares direct cell fill formatting, so it should not be used to count the displayed colors created by conditional formatting. Count the values or conditions behind the rule instead.

Why doesn’t my color-count formula update after I change a fill?

Changing a fill is a formatting edit and may not trigger formula recalculation. Recalculate or re-enter the formula to refresh it, but for dependable automatic updates, count a status or other value rather than formatting.

Can Excel for the web run the VBA counting function?

No. Excel for the web can open and edit many workbooks that contain macros, but it cannot create, run, or edit VBA macros. Use desktop Excel for the VBA method, or use a filter or helper-column formula instead.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.