How to count colored cells in Excel

Why COUNTIF can't count colors

Fill color is formatting, not part of the cell's value. COUNTIF, SUMIF and every other worksheet function look only at values, and changing a color does not even make Excel recalculate. There is no hidden criterion like "red" that works.

A few things look as if they should work and don't. CELL("color", A2) only reports whether the cell's number format shows negative numbers in color; it says nothing about the fill. The old GET.CELL macro function can return a fill color number, but only through a defined name in Name Manager, only in desktop Excel, only in a workbook saved as .xlsm, and it does not update when you recolor a cell until something forces a recalculation. A VBA function has its own catch, covered in the FAQ.

So the honest options are: count the colored cells by hand with a filter or Find, or count the condition the color was showing in the first place. The invoice list here has the overdue invoices filled red: Kestrel Media, Bluefin Studio, Juniper Books, Maple & Co and Summit Tutoring.

Method 1: Filter by Color, then SUBTOTAL

This is the fastest one-off answer and it works on any fill, including colors added by conditional formatting. Once the list is filtered, the status bar at the bottom left shows something like "5 of 10 records found". For a number you can use in the sheet, SUBTOTAL ignores the rows the filter hides.

Use 103 to count non-empty cells and 109 to sum. Put the formula above the header or in a cell beside the data, never inside the rows being filtered, or it disappears with them. With no filter on, the count below returns every invoice; filtered to red it returns only the red ones.

The catch: the result is only right while the filter is on, and only for colors that are exactly the same. A slightly different red, picked from More Colors, is a separate entry in the Filter by Color menu.

1. Click any cell in the list and choose Data > Filter (Ctrl+Shift+L).
2. Click the filter arrow on the Invoice column, then Filter by Color, and pick the red fill.
3. In H4, type =SUBTOTAL(103,A2:A11) to count the visible invoices, and in H5 =SUBTOTAL(109,C2:C11) to total their amounts.
4. Clear the filter with Data > Clear when you're done. The SUBTOTAL formulas go back to counting every row.

Method 2: Find All by format

Find can search by fill color and tells you how many cells it found, with no filter and no formula. It is handy when the colored cells are scattered across columns, which a filter cannot handle.

Two gotchas. Choose Format From Cell copies the whole format, so a cell with the same red but a different font or number format will not match; picking just the fill on the Fill tab avoids that. And Find only sees colors you applied directly. Cells colored by conditional formatting are not found, so use Method 1 or Method 3 for those.

1. Select the range to search, then press Ctrl+F (Home > Find & Select > Find).
2. Click Options, then the Format button next to Find what.
3. On the Fill tab, pick the red, or click Choose Format From Cell and click a red cell. Leave Find what empty and click OK.
4. Click Find All. The bottom of the dialog shows the count, such as "5 cell(s) found".
5. To add them up, press Ctrl+A while the results list is active to select every found cell, close the dialog and read Sum and Count in the status bar.

Method 3 (best): count the rule behind the color

Colors almost always mean something: overdue, over budget, missing, failed. Whatever made a cell red, by hand or with a conditional formatting rule, can be written as a condition. Count that condition and the number is always right, needs no filter, and updates the moment the data changes.

Here an invoice is red when it has no paid date and its due date is before the as-of date in H2. COUNTIFS takes those two tests directly. A blank criterion ("") matches empty cells, and "<"&H2 compares each due date with the as-of date. In a live sheet, use "<"&TODAY() instead of H2.

If the red came from a conditional formatting rule, open Home > Conditional Formatting > Manage Rules to read its formula, then copy its logic into COUNTIFS. The color and the count can then never disagree.

Sum by color with a helper column

When the rule has several parts, or you want to count and sum several colors, give it its own column. Column F labels each invoice Paid, Overdue or Open with one IF formula, and SUMIF or COUNTIF can then use the label like any other value. The same column can drive the conditional formatting (a rule such as =$F2="Overdue"), so the color follows the data.

For cells someone colored by hand with no rule at all, the fix is to stop using color as data. Add a Status column with a drop-down (Data > Data Validation > List, with Paid,Overdue,Open as the source), have people pick a status instead of a color, and let conditional formatting color the rows from it. You get the same red rows and a count that works with COUNTIF, PivotTables and filters.

Which method to pick

One quick count on a sheet you received: filter by color and read the status bar, or use SUBTOTAL if you need the number in a cell. Colored cells spread over many columns: Find All. Anything you will count again next week, or a report other people read: count the condition with COUNTIFS, or a helper status column, and let the color come from the same rule.

Avoid building reports on hand-applied color. It is invisible to formulas, lost when data is pasted as values or exported to CSV, and easy to get slightly wrong, since two reds that look identical can be different colors.