Guides

Average, min, max and median by color in Google Sheets

AVERAGEIF and MAXIFS can’t read colour. Here are the three ways to get the average, smallest, largest or median value of the cells with one colour, and where each one falls short.

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:

  1. Data → Create a filter, then the column’s filter icon → Filter by color, and pick the colour.
  2. Select the cells that stay visible.
  3. Read the figure in the status bar, bottom-right. Click it to switch between Sum, Average, Min, Max and Count.
Limits: one colour and one number at a time, the result doesn’t stay in the sheet, and you repeat the steps every time the data or the colours change. Fine for a quick look, unworkable for a sheet that has to report these figures.

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:

/** * Average of the numbers in cells whose fill colour matches a reference cell. * Pass both arguments as text: =AVERAGEBYFILL("B2:B100", "A2") */ function AVERAGEBYFILL(rangeA1, colorA1) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var target = sheet.getRange(colorA1).getBackground(); var range = sheet.getRange(rangeA1); var colors = range.getBackgrounds(); var values = range.getValues(); var sum = 0, n = 0; for (var r = 0; r < colors.length; r++) { for (var c = 0; c < colors[r].length; c++) { if (colors[r][c] === target && typeof values[r][c] === "number") { sum += values[r][c]; n++; } } } return n ? sum / n : 0; }

Back in the sheet: =AVERAGEBYFILL("B2:B100", "A2").

Watch out for:
  • 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:

# average of the cells that share the fill colour of A2 =AVERAGEBACKGROUNDCOLOR("B2:B100", "A2") # largest, smallest and median of the cells with that fill colour =MAXBACKGROUNDCOLOR("B2:B100", "A2") =MINBACKGROUNDCOLOR("B2:B100", "A2") =MEDIANBACKGROUNDCOLOR("B2:B100", "A2") # give a hex colour directly, or use the text colour instead of the fill =AVERAGEBACKGROUNDCOLOR("B2:B100", "#f4cccc") =MAXFONTCOLOR("B2:B100", "A2")

What the add-on version does for you:

The full list is on the function reference.

One limit that applies to every method: none can be used inside 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