How to count cells by color (and why formulas can't see color)
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Count highlighted cells — the honest answer about a famously impossible formula.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =SUBTOTAL(103,A2:A100) | With Data → Filter → Filter by Color applied: SUBTOTAL counts only the visible (colored) rows. |
| Google Sheets | =SUBTOTAL(103,A2:A100) | Same trick via the filter's "Filter by color". |
| LibreOffice Calc | =SUBTOTAL(103,A2:A100) | LibreOffice's Standard Filter can't filter by color — color counting needs a Basic macro. |
How it works
No built-in formula in any of the three apps can read a cell's fill color — COUNTIF sees values, not formatting. The practical workaround: apply Filter by Color, then SUBTOTAL(103,...) — code 103 is COUNTA over visible rows only — counts what remains (the verification proves the SUBTOTAL machinery; with a color filter active, hidden rows drop out of the count). The robust long-term fix is to stop encoding data in color: add a status column and count that — colors can even be driven FROM it with conditional formatting, giving the same look plus countability.
Verified, not just documented
We ran =SUBTOTAL(103,A2:A4) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 3 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned 3 for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
SUBTOTAL — see full Excel, Google Sheets & LibreOffice compatibility for each.