Your numbers are colour-coded — green for done, red for late,
yellow for pending — and now you want the average
of the green ones, the highest of the red, or the
median of the yellow. Google Sheets has
AVERAGEIF and MAXIFS, but they test values,
never colours. You have three options.
Method 1 — Filter by color, then read the status bar
No formula, no code:
- Data → Create a filter, then the column’s filter icon → Filter by color, and pick the colour.
- Select the cells that stay visible.
- Read the figure in the status bar, bottom-right. Click it to switch between Sum, Average, Min, Max and Count.
Method 2 — A custom function you paste yourself
Free and the number stays in the sheet — but this snippet only averages by fill colour. Open Extensions → Apps Script, paste this, and save:
Back in the sheet: =AVERAGEBYFILL("B2:B100", "A2").
- The range must be passed as a string (
"B2:B100") — a normal range argument gives Apps Script only the values, never the formatting. - Changing only a cell’s colour does not recalculate the sheet, so the number goes stale until you edit a value or reopen the file.
- Min, max and median each mean another near-identical function, and text colour or font style mean more again.
- The script lives in this spreadsheet only. Every other file you want it in, you paste it again and re-authorise; every collaborator does the same in their copy.
Method 3 — The Custom Count and Sum add-on
Install once and the functions are in every spreadsheet on your account — now and later — with no code to paste or keep working. After installing Custom Count & Sum:
What the add-on version does for you:
- all four aggregations for fill colour, text colour, font style, and colour and style together — for example
AVERAGEFONTCOLOR,MINSTYLEorMAXCOLORANDSTYLE; - only the numeric values of the matching cells are used, and the result is
0when no cell matches, not an error; - the colour comes from a reference cell or a hex code;
- several ranges at once —
=MAXBACKGROUNDCOLOR({"B2:B100","E2:E100"}, "A2").
The full list is on the function reference.
MAP, BYROW or other
LAMBDA functions — Google runs those entirely in the
calculation engine, which has no access to a cell’s formatting.
Which method should you use?
| Filter + status bar | Own script | Add-on | |
|---|---|---|---|
| Setup | None | Paste & maintain code | One-click install |
| Lives in the sheet as a number | No | Yes | Yes |
| Updates after a colour change | Redo by hand | Only with a custom trigger | Yes, via the Recalculate button |
| Average, min, max and median | One number at a time | A function each | Built in |
| Available in your other spreadsheets | — | No — paste per file | Yes — every sheet on your account |
| Upkeep | Redo by hand | You own every fix & addition | Updates itself |
| Cost | Free | Free | These functions need a plan after a 14-day free trial |
The status bar is enough for a one-off look at a small range. A custom function works if you need one aggregation by fill colour and don’t mind the recalc caveat. Reach for the add-on when the figures have to stay in the sheet and you also need min, max, median or text colour.
Average and rank colour-coded cells without a script
Custom Count and Sum adds 38 formatting-aware functions to Google Sheets. COUNTSTYLE is free on any range; a 14-day trial unlocks the rest.
Install free from Google Workspace Marketplace