Count Excel Cells by Color: Practical Methods

Excel has no built-in COUNTBYCOLOR function. For a quick count in Microsoft 365 for Windows, filter the column by its fill color, then use =SUBTOTAL(103,B2:B100) to count visible, nonblank cells; for a repeatable count that updates from a sample color, use a VBA function or count the rule that creates the color.

What Excel Can—and Cannot—Count

Color is formatting, not a cell value. Ordinary worksheet formulas such as COUNTIF and COUNTIFS evaluate values or conditions; they do not provide a standard way to ask whether a cell has a particular manually applied fill color. That is why a formula like COUNTIF cannot simply be given a red fill as its criterion.

Before choosing a method, distinguish between two kinds of color. A direct fill is applied with the Fill Color button or the Format Cells dialog. A conditional-formatting color is displayed because a rule evaluates the cell or another value. These may look identical, but they are produced differently, and some counting techniques can read one but not the other.

Also decide what you mean by “count.” You might want the number of colored cells, the number of colored cells containing values, or the number of records whose key cell has a color. Filtering and SUBTOTAL are convenient for visible nonblank entries. A VBA function can count colored cells even when they are empty.

Quick Method: Filter by Fill Color

Filtering is usually the best choice when you need a one-time count and the colored cells are in a single data column. It does not require code or a special formula that reads formatting. It works by displaying rows whose cells match the chosen color, after which SUBTOTAL counts the visible entries.

Make sure the column has a header and that each row represents one record. Click inside the data range.
On the ribbon, select Data > Filter. Excel adds a dropdown to each header.
Open the dropdown for the column with the fill colors, choose Filter by Color, and select the fill you want to count.
In a cell outside the filtered data, enter the formula below. With the relevant entries in B2:B100, it counts visible, nonblank cells in that range.

With values in B2:B100 and that range filtered to the target fill, =SUBTOTAL(103,B2:B100) returns the number of visible nonblank cells. For example, if seven matching rows have a value in column B, the result is 7.

Function number 103 tells SUBTOTAL to count nonempty cells while ignoring rows hidden by a filter and rows hidden manually. If you want manually hidden rows included, use function number 3 instead: =SUBTOTAL(3,B2:B100). Both versions still exclude rows removed by the filter. If the range contains only numbers, function number 102 counts visible numeric cells; function number 2 does the same while including manually hidden rows.

The count is based on the filtered data, so clearing the color filter changes the result. This method counts entries, not empty colored cells: COUNTA-style counting excludes a cell that has a fill but no value. To count colored blanks too, use a different approach such as the VBA method below.

Count Cells, Not Just Filled-In Records

A colored blank cell can matter in a template—for example, a blank red cell might mark a required field that still needs attention. SUBTOTAL with 103 will not count it, because that function counts nonempty cells. A filtered list may show rows with the selected color, but the SUBTOTAL result will still omit blank cells in the counted column.

One workaround is to add a helper column that contains a value for every data row, then filter the color column and apply SUBTOTAL to the helper column. This counts matching records even if the colored cell itself is blank. It is only reliable when each record has a corresponding helper value and the color filter selects the intended rows.

For an exact count of individual colored cells, including blanks, a VBA function can compare each cell’s fill with a sample cell. That is useful for a small range or a workbook where color is being used as a deliberate label. It is not a substitute for a well-designed data field when the color represents an important business status.

Repeatable Count with a VBA Function

A user-defined VBA function can compare the direct fill color of every cell in a range against a sample cell. In the example below, the data is in B2:B100 and E1 has the fill color to match. The function counts matching cells whether they contain values or are blank.

In Excel for Windows, press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, and paste the code into the module. Save the workbook as an Excel Macro-Enabled Workbook (.xlsm). Save a separate copy before running or adding macros: macros cannot be undone with Ctrl+Z.

Function CountFillColor(DataRange As Range, SampleCell As Range) As Long
    Dim cell As Range

    Application.Volatile
    For Each cell In DataRange.Cells
        If cell.Interior.Color = SampleCell.Interior.Color Then
            CountFillColor = CountFillColor + 1
        End If
    Next cell
End Function

With the range B2:B100 and the sample fill in E1, enter =CountFillColor($B$2:$B$100,$E$1) in a worksheet cell. If four cells in B2:B100 have the same direct fill as E1, the result is 4, including any blank cells among those four. The sample cell itself is not included unless it is part of the range.

Application.Volatile asks Excel to recalculate the function during calculation, but changing only a cell’s formatting does not reliably trigger recalculation. After changing a fill or the sample color, force a calculation with F9, or edit the formula and press Enter. On Mac, recalculation shortcuts can differ according to keyboard settings; use the Formulas tab’s calculation commands if needed. Excel for the web does not run VBA macros or these custom functions. Excel for Mac supports VBA in the desktop app, although opening the editor and using function-key shortcuts can differ from Windows.

Conditional Formatting Needs Special Treatment

Conditional formatting changes the displayed appearance according to a rule; it does not necessarily change the cell’s underlying direct fill. The VBA function above reads Interior.Color, which is the direct fill, so it does not count a color merely because a conditional-formatting rule displays it.

When a conditional format is based on a clear condition, count the condition rather than the appearance. For example, if cells in B2:B100 turn red when their values are below 0, the number of red cells can be counted with =COUNTIF(B2:B100,"<0"). That formula assumes the same below-zero rule applies to the whole range; it returns the number of negative values, not a reading of the displayed color. If the formatting rule is more complex, build a count formula that mirrors its logic, taking care to match the rule’s relative references and order.

If the rule depends on another column, use that column’s condition in the count. For example, when values in B2:B100 are red whenever the corresponding status in C2:C100 is “Late,” count the status values rather than trying to inspect the red fill. If several rules, priorities, or “Stop If True” settings affect the visible result, a single simple condition may not reproduce the formatting; review the rules through Home > Conditional Formatting > Manage Rules.

Choosing the Right Method

One-time count of nonblank entries: Filter by Color and use SUBTOTAL. It is visible, easy to verify, and needs no macro.
Count every directly colored cell, including blanks: Use the VBA function, provided macros are allowed and the workbook can be saved as .xlsm.
Color comes from a conditional-formatting rule: Count the underlying values or conditions that drive the rule.
Recurring reporting or shared files: Store the category in a normal data column, such as Status, and use COUNTIF or a PivotTable. This avoids relying on formatting as data.

For example, a task list can use a Status column with values such as “Late,” “In progress,” and “Done,” while conditional formatting colors late tasks red. Counting “Late” is more dependable than counting red cells: it works even if someone changes the theme color, prints the sheet in grayscale, or opens it in software that renders formatting differently.

Common Problems and Checks

The result is zero or too small: Check whether the counted range includes the intended rows and whether the cells are truly nonblank. SUBTOTAL with 103 excludes blank cells.
The color filter is missing: Confirm that filtering is enabled for the right column and that the cells have a consistent fill. A table header or a clean rectangular data range helps Excel identify the filter area.
Colors look the same but do not match: Similar shades can have different color values. Choose the sample cell from the actual range you want to count rather than a visually similar color elsewhere.
A macro result seems stale: Recalculate after changing formatting. A formatting-only change may not automatically prompt a worksheet UDF to run.
The VBA result misses conditional colors: That is expected; the example reads direct fills, not conditional-formatting display colors.
Filtered counts include the wrong records: Check that the filter is on the color column and that SUBTOTAL references the matching rows, not an unrelated range.

Color can also be a poor sole indicator for accessibility and analysis. A reader may not distinguish certain colors, and formatting can be changed without changing the underlying meaning. Pair color with a label, icon, or status value when the distinction matters to other people or feeds later calculations.

Frequently Asked Questions

Can COUNTIF count cells by color?
No. COUNTIF evaluates cell values against a condition and does not read a manually applied fill. Count the underlying condition when possible, or use filtering or VBA for direct formatting.

Can I count cells by color without VBA?
Yes. Filter the relevant column with Filter by Color and use SUBTOTAL to count visible nonblank entries. For a reusable count of exact direct fills that includes blanks, standard worksheet formulas do not provide a simple built-in color-count function.

Does the VBA function count conditional-formatting colors?
No. The sample function compares direct cell fills through Interior.Color. To count cells colored by a rule, count the values or conditions that make the rule apply.

Will the count update when I change a cell’s fill?
A filter-based result changes when you change the filter. A VBA function marked volatile may need recalculation after a formatting-only change; press F9 on Windows or use the calculation controls. A direct fill change alone is not a dependable recalculation trigger.

Can Excel for the web count by color?
You can use filtering features available in the web app, but it does not run VBA macros or custom VBA worksheet functions. For a dependable shared workbook, use a data field for the category and count that value instead of relying on fill color.

Make Color a Display Choice, Not the Data

For a quick answer, filter by the target fill and count visible nonblank entries with SUBTOTAL. For exact counts of directly colored cells, including empty ones, a VBA function can compare fills, with the limitations of macros and recalculation. When the color communicates a real category or status—especially when it comes from conditional formatting—count the underlying data or rule instead. That makes the result easier to audit, share, and maintain.

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.