How to Count and Sum Colored Cells in Excel
You've colour-coded a sheet by hand and now want "how many green, and what do they total?" Excel has no COUNTIF for colour — here are the real ways, and a one-click report.
Last updated: August 2026
| Colour | Count · Sum | |
|---|---|---|
| Green | → | 12 cells · 4,300 |
| Amber | → | 5 cells · 1,100 |
| Red | → | 3 cells · 620 |
Method 1 — Filter by Color + SUBTOTAL
- Turn on filters (Data > Filter), click the column arrow, and choose Filter by Color.
- With one colour showing:
=SUBTOTAL(9, range)sums the visible cells,=SUBTOTAL(2, range)counts them. - Repeat for each colour. Tedious, but formula-only.
Method 2 — the GET.CELL macro trick
Define a name like CellColor =GET.CELL(38, Sheet1!A1), put =CellColor in a helper column, then COUNTIF/SUMIF on the colour codes. It works, but it's an old XLM macro function, the workbook must be saved as .xlsm, and it doesn't recalc reliably when you recolour.
The 1-click way — Tellsheet
Sum & Count by Color groups the selected cells by their fill or font colour and writes a clean report to a new sheet: per colour, how many cells carry it and the total of their numbers. No macros, no .xlsm. Note: like every add-in and formula, it reads only manually applied colours — conditional-formatting colours can't be read by any tool. Pairs with Group & Summarize.
Frequently asked questions
Is there a COUNTIF for colour?
No native one. Use Filter by Color + SUBTOTAL, the GET.CELL macro, or Tellsheet's Sum & Count by Color for a direct per-colour report.
Can I count conditional-formatting colours?
No tool can — Excel doesn't expose them. Count on the underlying rule instead.
Can I sum by font colour, not just fill?
Yes — Sum & Count by Color has a fill/font switch.
Does it change my data?
No — it only reads colours and writes a separate report sheet.
Related Excel guides
Count by colour in one click
Sum & Count by Color reports every colour's count and total on a new sheet — no macros, right inside Excel.
Get Tellsheet free See pricing