How to Count Colored Cells in Excel

To count colored cells in Excel, filter the column by that color with Data > Filter > Filter by Color, then enter =SUBTOTAL(103,A2:A101) in an empty cell. SUBTOTAL counts only the rows left visible by the filter, so the result is the number of nonblank cells with that fill color.

Excel has no built-in function that reads a cell’s background color, and COUNTIF can’t do it either. The methods below work around that. The steps use Excel for Microsoft 365, Excel 2024, 2021 and 2019 on Windows. Excel for Mac and Excel for the web also have Filter by Color, so the filter method works there too; the VBA method only works in desktop Excel.

Which Method Should You Use?

  • Filter and SUBTOTAL is the best choice for most people. It needs no macros and works on any fill color you applied by hand.
  • The helper column version counts colored rows even when the colored cell is empty.
  • COUNTIF on the rule is the cleanest answer when the color comes from conditional formatting, because you count the condition instead of the color.
  • A VBA function gives you a formula you can reuse, but it means saving a macro-enabled workbook.

Method 1: Count Colored Cells With Filter and SUBTOTAL

In this example, A1 is a heading and A2:A101 holds the data. Swap in your own range.

  1. Select your data, starting with the heading in A1. If the data has related columns, select the whole block so rows stay together.
  2. Go to the Data tab and click Filter. A small dropdown arrow appears next to each heading. (If your data is already an Excel table, the arrows are already there.)
  3. Click the arrow in the heading of the colored column.
  4. Point to Filter by Color, then click the color you want under Filter by Cell Color. Rows with any other fill are hidden.
Excel filter menu showing Filter by Color with cell color swatches
Choose the swatch under Filter by Cell Color, not Font Color.
  1. Click an empty cell above or beside the data, such as D1, and type =SUBTOTAL(103,A2:A101).
  2. Press Enter. The cell shows how many visible cells in A2:A101 have something in them, which is your count of that color.
Excel sheet filtered by yellow fill with SUBTOTAL count in cell D1
SUBTOTAL counts only the rows left visible by the color filter.

Keep the formula cell outside the counted range and out of the rows the filter might hide. A formula in row 50 disappears when row 50 is filtered out.

The number 103 tells SUBTOTAL to use COUNTA (count nonblank cells) and skip hidden rows. Microsoft’s SUBTOTAL reference confirms that filtered-out rows are always excluded, and the 100-series numbers also skip rows you hid by hand.

To count another color, open the same dropdown, choose Filter by Color again, and pick the next color. The SUBTOTAL result updates on its own. Write each result down with its color, because the number changes as soon as the filter does.

When you’re finished, click the dropdown and choose Clear Filter From “(your heading)” to show every row again. Filtering only hides rows; it doesn’t delete anything. Microsoft’s guide to filtering data in a range or table covers the other filter options.

Quick Check Without a Formula

After filtering, select the visible colored cells and look at Count on the status bar at the bottom of the window. It counts only visible, nonblank cells. If you don’t see it, right-click the status bar and check Count.

Method 2: Count Colored Rows That Include Empty Cells

COUNTA ignores empty cells, so a yellow cell with nothing in it won’t be counted by Method 1. A helper column fixes that.

If the column beside your data is already in use, insert a blank column in Excel for the helper column first.

  1. In an unused column next to your data, such as column B, type a heading like Count Me in B1.
  2. Type 1 in B2, then fill it down to B101 so every data row has a 1.
  3. Make sure the filter covers both columns (A1:B101). If you turned on the filter earlier, turn it off and on again with the whole block selected.
  4. Filter column A by the cell color you want, as in Method 1.
  5. In an empty cell outside the data, enter =SUBTOTAL(109,B2:B101) and press Enter.

The 109 tells SUBTOTAL to add up the visible values, and since each row has a 1, the total equals the number of colored rows, including any that are blank in column A.

Method 3: Count by the Rule Behind Conditional Formatting

If a conditional formatting rule colors the cells, count the rule’s condition instead of the color. Say the rule turns values over 100 green. This formula gives the same count without any filtering:

=COUNTIF(A2:A101,">100")

Match the formula to the rule exactly. A rule of “greater than or equal to 100” needs ">=100", and that one character changes the total. For rules with more than one condition, use COUNTIFS. To check what a rule says, select a colored cell and go to Home > Conditional Formatting > Manage Rules. Type formulas with straight quotes (“), not curly ones, or Excel won’t accept them.

If you’re setting up colors from scratch, this approach is the most reliable over time. To build rules you can count this way, read about using IF formulas to color cells in Excel.

Method 4: Count Colored Cells With a VBA Function

A short custom function lets you count by color with a normal formula. This is for desktop Excel on Windows or Mac, and the file must be saved as an Excel Macro-Enabled Workbook (.xlsm).

On a Mac, you can enable the Developer tab in Excel for Mac to reach the Visual Basic button from the ribbon.

  1. Save a backup copy of your workbook.
  2. Press Alt + F11 to open the Visual Basic Editor.
  3. Click Insert > Module.
  4. Paste this code into the blank module window:

Function CountColor(rng As Range, sample As Range) As Long
    Dim c As Range
    Application.Volatile
    For Each c In rng
        If c.Interior.Color = sample.Interior.Color Then CountColor = CountColor + 1
    Next c
End Function

Excel Visual Basic Editor with CountColor function code
Paste the function into a new module.
  1. Close the editor and return to your sheet.
  2. Fill one empty cell, such as D2, with the color you want to count.
  3. In another cell, enter =CountColor(A2:A101,D2) and press Enter. The result is the number of cells in A2:A101 with the same fill as D2, including empty ones.
  4. Click File > Save As and choose Excel Macro-Enabled Workbook (*.xlsm).

Two limits to know. Changing a fill color doesn’t trigger a recalculation, so press F9 after recoloring cells to refresh the count. And this function reads the fill you applied by hand; it doesn’t see colors added by conditional formatting, so use Method 3 for those.

If Excel blocks the macro when you reopen the file, click Enable Content in the yellow security bar. Some workplaces turn off macros entirely, in which case stick with Method 1.

Why the Count Looks Wrong

  • Some colored cells are empty. SUBTOTAL 103 skips blanks. Use the helper column in Method 2.
  • Cells with formulas that return “” are counted. COUNTA treats an empty-looking formula result as filled. The helper column avoids this too.
  • Another filter is still on. A filter in a different column hides more rows and lowers the count. Click Data > Clear to remove all filters, then filter by color again.
  • You picked font color instead of fill color. The Filter by Color menu has separate Cell Color and Font Color sections. Choose the cell color.
  • Two shades look the same. Light yellow and a slightly different yellow show as separate swatches. Check every swatch in the filter list.
  • The range includes the heading or misses the last rows. Start the formula on the first data row and end it on the last one.

If you still need to apply the colors, fill the cells with color first.

To count cells by their value instead of their color, use COUNTIF in Excel.

Colored Cell Count Questions

Can COUNTIF count cells by color?

No. COUNTIF only looks at what’s in a cell, such as a number or text, not how the cell is formatted. Typing “yellow” as the criteria counts cells that contain the word yellow.

Can I sum colored cells instead of counting them?

Yes. Filter by color as in Method 1, then use =SUBTOTAL(109,A2:A101) on the column of numbers. It adds only the visible values.

Does the count update when I change colors?

With the filter method, reapply the filter after recoloring cells and the SUBTOTAL result updates. With the VBA function, press F9 after changing colors.

Can I count colored cells across several columns?

Filter by color works one column at a time. Filter each column, record its count, and add the results. For data you’ll report on often, add a status column (such as “Approved” or “Pending”) and count that with COUNTIF instead of relying on color.