You colour-code a list — paid in green, overdue in red — and
now you want just the green values somewhere else: in a summary, in
another range, or as input for another formula. FILTER
works on values and conditions; it can’t see a cell’s
colour. You have three options.
Method 1 — Filter by color from the menu
Google Sheets can hide every row that doesn’t have the colour you pick:
- Select the column, then Data → Create a filter.
- Open the column’s filter icon → Filter by color, and choose Fill color or Text color.
- Pick the colour. Rows with any other colour disappear.
Method 2 — A custom function you paste yourself
Free and it returns a real list — but you own the upkeep, and this snippet only reads the fill colour. Open Extensions → Apps Script, paste this, and save:
Back in the sheet: =FILTERBYFILL("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 list goes stale until you edit a value or reopen the file.
- Text colour, font style, or several ranges each mean another function or more code.
- 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:
- the matching values spill downward as a vertical list, with empty cells left out — keep the cells below the formula empty, as with any array formula;
- when nothing matches, the formula returns a single empty cell instead of an error;
- the colour comes from a reference cell or a hex code, so you don’t have to hard-code anything;
- several ranges at once —
=FILTERBGCOLOR({"B2:B100","E2:E100"}, "A2"); - font style and combinations too —
FILTERFONTSTYLEandFILTERCOLORANDSTYLE.
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 by color (menu) | Own script | Add-on | |
|---|---|---|---|
| Setup | None | Paste & maintain code | One-click install |
| Returns a list you can use in a formula | No — hides rows | Yes | Yes |
| Updates after a colour change | Re-apply by hand | Only with a custom trigger | Yes, via the Recalculate button |
| Text colour, style, several ranges | One colour at a time | A function each | Built in |
| Available in your other spreadsheets | — | No — paste per file | Yes — every sheet on your account |
| Upkeep | None | You own every fix & addition | Updates itself |
| Cost | Free | Free | The FILTER functions need a plan after a 14-day free trial |
The menu filter is enough when you only need to look at one colour. A custom function works if you need the fill colour only and don’t mind the recalc caveat. Reach for the add-on when the list has to live in the sheet, feed other formulas, or follow text colour or font style as well.
Return the cells of one colour with a formula
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