Can I count cells by their conditional formatting colour in Google Sheets?

Not by the colour itself. Conditional formatting is a display rule, not a stored fill — getBackground() in Apps Script returns the cell's base colour, not the one the rule paints, so no formula, script or add-on can read it. Instead, count by the rule's own condition, or convert the highlight into a real fill and count that.

How do I count cells highlighted by a 'greater than' conditional formatting rule?

Count by the same condition the rule uses. If the rule highlights values over 100, use =COUNTIF(A2:A100, ">100"); if it uses a custom formula, reuse that formula inside COUNTIF, SUMPRODUCT or FILTER.

Can Custom Count & Sum count conditional formatting colours?

No add-on can read a conditional-formatting colour. Custom Count & Sum counts real fills: COUNTBACKGROUNDCOLOR and COUNTFONTCOLOR work once the cells have an actual background or font colour, whether you applied it by hand or replaced the rule with a manual fill.

Count Cells by Conditional Formatting Color in Google Sheets

A rule turns cells green when they’re over target, and you want to count the green ones. The problem: conditional formatting is a display rule, not a stored fill. Google paints the colour on screen but never writes it to the cell, so getBackground() in Apps Script returns the cell’s base colour — and every “count by colour” tool, this add-on included, reads that same base colour. There is no hidden API for the rendered colour.

Approach 1 — Count by the rule’s condition, not its colour

The colour is just a visual stand-in for a condition you already defined. Count that condition directly and you get a live number with no add-on at all.

# rule: highlight when the value is greater than 100 =COUNTIF(A2:A100, ">100") # rule: highlight blanks =COUNTBLANK(A2:A100) # rule uses a custom formula, e.g. =AND($C2="open", $D2<TODAY()) =SUMPRODUCT((C2:C100="open") * (D2:D100<TODAY()))

Open Format → Conditional formatting, read the exact test the rule uses, and translate it into COUNTIF, COUNTIFS, SUMPRODUCT or FILTER.

Why this is the right answer: the count updates the instant the data changes — exactly like the highlight does — because it’s driven by the same logic. Reading a colour never gives you that.

Approach 2 — Turn the highlight into a real fill, then count that

If you genuinely need to work from colour — the rules are complex, or colours were applied by different people over time — make the fill real:

Select the range. Format → Conditional formatting, note each rule, then delete the rules.
Re-apply the same colours as an actual fill colour (manually, or with a one-off Apps Script that reads each rule and calls setBackground()).
Now the colour is stored in the cell, and counting cells by colour works normally.

# once the cells carry a real fill, with Custom Count & Sum: =COUNTBACKGROUNDCOLOR("A2:A100", "C1") # C1 holds the reference colour =COUNTBACKGROUNDCOLOR("A2:A100", "#00ff00")

Watch out for: once you drop the rules the colours stop updating themselves — a cell that later goes over target won’t turn green on its own. This approach only makes sense for a snapshot, or when the colour is a deliberate manual tag rather than a live indicator.

Which approach should you use?

	By the condition	Convert to real fill
Stays live as data changes	Yes	No — snapshot only
Needs an add-on	No	Only for the colour count itself
Handles many overlapping rules	Gets fiddly	Yes, once converted
Effort	Rewrite one condition	Strip rules + re-fill

Almost always, count by the condition — it’s less work and it stays correct. Convert to a real fill only when the highlighting has drifted away from any single rule you can restate, or when the colour is really a manual label.

Counting real fill colours

Once your cells carry an actual background or font colour, Custom Count & Sum counts, sums and averages by it — one install, every spreadsheet on your account, nothing to script. Free for formulas up to 30 cells.

Install free from Google Workspace Marketplace

Count cells by conditional formatting colour

Approach 1 — Count by the rule’s condition, not its colour

Approach 2 — Turn the highlight into a real fill, then count that

Which approach should you use?

Counting real fill colours