Is there a built-in function to count cells by color in Google Sheets?

No. Google Sheets has no native function that reads a cell's fill colour or font colour. You can count colours manually with Filter by colour, write your own Apps Script custom function, or install an add-on that provides functions such as COUNTBACKGROUNDCOLOR and COUNTFONTCOLOR.

Why does my count-by-color formula not update when I change a cell's colour?

Changing only a cell's formatting does not trigger a recalculation in Google Sheets, so a custom function that reads colour will show a stale result until something else forces a recalc. Editing a value, reopening the file, or a dedicated refresh action updates it. Custom Count & Sum ships a refresh so its colour formulas stay current.

Can I sum values by cell colour instead of counting them?

Yes. The same approaches apply: SUMBACKGROUNDCOLOR("A2:A100", "C1") sums the values of every cell in A2:A100 whose fill colour matches C1, and SUMFONTCOLOR does the same for font colour. Averages, minimums, maximums and medians by colour are also available.

Count or Sum Cells by Color in Google Sheets (2026)

You have a column shaded green for “done” and red for “blocked”, and you want a live count of each. Unlike SUMIF or COUNTIF, there is no worksheet function that looks at a cell’s fill colour or font colour — colour isn’t a value the formula engine can see. You have three options.

Method 1 — Filter by color and read the status bar

Good for a one-off check. Nothing to install.

Select your range and choose Data → Create a filter.
Click the filter icon in the column header, then Filter by color → Fill Color (or Text Color) and pick the colour.
Select the now-visible cells. The count appears bottom-right in the status bar; click it to switch between Count, Sum, Average and so on.

Limits: it doesn’t stay in the sheet as a number, it doesn’t update when the data changes, it handles one colour at a time, and a filter is visible to everyone else viewing the file.

Method 2 — A custom function you paste yourself

Free and it stays in the sheet — but you also own the upkeep, and this snippet only counts. Open Extensions → Apps Script, paste this, and save:

/** * Count cells in countRange whose fill colour matches colorRef. * Pass BOTH arguments as text strings: * =COUNTBGCOLOR("B2:B100", "D1") */ function COUNTBGCOLOR(countRangeA1, colorRefA1) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var target = sheet.getRange(colorRefA1).getBackground(); var bgs = sheet.getRange(countRangeA1).getBackgrounds(); var n = 0; bgs.forEach(function (row) { row.forEach(function (bg) { if (bg === target) n++; }); }); return n; }

Back in the sheet: =COUNTBGCOLOR("B2:B100", "D1").

Watch out for:

The range must be passed as a string ("B2:B100"), because a normal range argument gives Apps Script only the values, never the colours.
Changing only a colour does not recalculate the sheet, so the number goes stale until you edit a value, reopen the file, or add an onEdit trigger that forces a refresh.
It reads exact hex colours, so a “green” applied by conditional formatting (which isn’t a real fill) won’t be matched — only manual fills count.
It only counts. A sum, average or min/max by colour is a separate near-identical function each — and font colour instead of fill is another one 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 & Sum add-on

Install once and the functions are in every spreadsheet on your account — the ones you have now, the ones you make later, and the ones people share with you — with no code to paste or keep working. You get 38 formatting-aware functions covering every aggregation, plus a refresh so colour changes actually register. After installing Custom Count & Sum:

# count cells in A2:A100 whose fill colour matches C1 =COUNTBACKGROUNDCOLOR("A2:A100", "C1") # count by font colour instead =COUNTFONTCOLOR("A2:A100", "C1") # sum / average the values of the matching cells =SUMBACKGROUNDCOLOR("A2:A100", "C1") =AVERAGEBACKGROUNDCOLOR("A2:A100", "C1")

Beyond the basic count, the add-on version adds:

a plain hex string instead of a reference cell — =COUNTBACKGROUNDCOLOR("A2:A100", "#46bdc6");
several ranges at once — =COUNTBACKGROUNDCOLOR({"A2:A100","D2:D100"}, "C1");
colour and font style in one test with COUNTCOLORANDSTYLE;
MIN, MAX and MEDIAN by colour, plus cross-sheet variants.

The full list is on the function reference.

One shared limit for every method above: none can see a colour that comes from conditional formatting, because that is a display rule, not a stored fill. To count by a rule, test the same condition the rule uses (for example =COUNTIF(A2:A100, ">100")).

Which method should you use?

	Filter by color	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	Manual re-filter	Only with a custom trigger	Yes, via built-in refresh
Multiple colours / ranges, sum & average	One at a time	More code	Built in
Available in your other spreadsheets	—	No — paste per file	Yes — every sheet on your account
Upkeep	Re-filter each time	You own every fix & addition	Updates itself
Cost	Free	Free	Free up to 30 cells per formula, then a plan

Use Filter by color for a one-off look, a custom function if you need one colour counted in one sheet and don’t mind the recalc caveat and maintaining the code, and the add-on when colour is part of how you track work, you want sums and averages too, or you need the same functions across more than one spreadsheet.

Count by colour without writing a script

Custom Count & Sum adds 38 formatting-aware functions to Google Sheets. Free for formulas up to 30 cells.

Install free from Google Workspace Marketplace

Count or sum cells by color in Google Sheets

Method 1 — Filter by color and read the status bar

Method 2 — A custom function you paste yourself

Method 3 — The Custom Count & Sum add-on

Which method should you use?

Count by colour without writing a script