How to count conditional formatting colors in Google Sheets

By Jake Marsh · Updated

To count cells colored by conditional formatting in Google Sheets, use Huecount's =COUNTBYCOLOR(range, color). It counts the color each cell shows on screen, working out which rule paints it the way Sheets does, so a rule that turns overdue dates red is counted with =COUNTBYCOLOR(D2:D200, "red") or a sample cell in that red. Scripts that read a cell's background only see the fill set by hand, which is why they return 0 for rule colors.

Why rule colors are hard to count

A conditional formatting rule doesn't change the cell's own fill. It paints over it while the condition is true. Apps Script's getBackgrounds(), which most copied count-by-color scripts use, returns the fill underneath, so a column that looks half red counts as all white. Huecount reads the rules themselves and works out the result for every cell: rules in priority order, the first true rule wins, and color scales are worked out per cell.

Steps

  1. Install Huecount and open the spreadsheet with the rules.
  2. Open Extensions > Huecount > Open Huecount, select the range and click Use selection.
  3. Leave Include conditional formatting (count the colors you see) ticked and click Count colors. Each color the rules produce gets a line with its cell count.
  4. Click the color and insert the formula, or type it: =COUNTBYCOLOR(D2:D200, F1) where F1 is a cell colored the same way.
  5. Tick Recalculate automatically when colors change, then Also after edits, when this file uses conditional formatting. A rule's color changes when values change, so the second box makes Huecount rerun after edits too.

Which rules Huecount evaluates

Rule typeCounted
Text contains, starts with, ends with, is exactlyYes
Greater than, less than, between, equal toYes
Date is before, after, or exactlyYes
Is empty, is not emptyYes
Color scalesYes, the shade each cell gets
Custom formula with AND, OR, IF, COUNTIF, ISBLANK, TODAY, ROW, MOD, SEARCH, REGEXMATCH and similarYes
Custom formula using VLOOKUP, XLOOKUP, MATCH, INDEX or FILTERNo

When a rule can't be evaluated, the formula shows an error that names the rule instead of a number that may be wrong. Add "skipcf" to ignore just that rule, or "nocf" to count each cell's own fill and ignore every rule.

Example

A task list where a custom formula rule =AND($C2<TODAY(), $D2<>"Done") turns late rows red, and a second rule colors rows with "Done" in column D green. =COUNTBYCOLOR(A2:A120, "red") counts late tasks, and once auto recalculation is on, marking a task Done moves it from the red count to the green one without anyone touching a fill.

Rule colors work the same for totals: =SUMBYCOLOR(A2:A120, "red", E2:E120) adds the hours on late tasks.

Color scales

A color scale gives every cell its own shade, so counting one exact shade is rarely useful. Use "tolerance=N" to group nearby shades, or =CELLCOLOR(A2:A120) to write each cell's shade next to it and work with the codes.

What it costs

Huecount's trial is 14 days or 50 color breakdowns in the sidebar, whichever comes first, with no card. The formulas work through the whole trial. Then it's $2.99 a month, $19 a year, or $49 once for lifetime access per Google account, or $99 a year for everyone at your company or school domain. Details and install are on the Huecount page.

Huecount: Count & Sum by Color

Count and sum cells by color in Google Sheets, with formulas that update when colors change $2.99 a month or $19 a year per Google account.


Questions

Can Google Sheets count cells colored by conditional formatting?

Not by itself. Sheets has no color function, and scripts that read backgrounds see only the fill underneath a rule. Huecount counts the color the rule shows.

Can I count only the cells I filled by hand?

Yes. Add the "nocf" option, for example =COUNTBYCOLOR(A2:A120, "yellow", "nocf").

What happens when a rule uses a function Huecount doesn't know?

The formula returns an error that names the rule. Add "skipcf" to leave that rule out, or email hello@greatwork.company with the rule so it can be added.

Do alternating colors count as conditional formatting?

No. Colors from Format > Alternating colors aren't read, so those cells count as their own fill.


More Huecount guides


More Google guides