// Sheets · Apps Script

Count cells by background color in Google Sheets.

Use Apps Script's getBackgrounds() to count cells by fill color in Google Sheets — and learn why conditional-formatting colors are invisible to it.

“I need to count how many cells in a column share the same background color, and the built-in COUNTIF has no color argument.”

The script

copy · paste · trigger
countByColor.gs
Apps Script
// Count cells in a range whose background matches targetHex.
// Call from a sheet cell: =COUNT_BY_COLOR("#ea4335", A1:A50)
// targetHex must be a lowercase 6-digit hex string, e.g. "#ea4335".
function COUNT_BY_COLOR(targetHex, range) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var backgrounds = sheet.getRange(
    range.getRow(),
    range.getColumn(),
    range.getNumRows(),
    range.getNumColumns()
  ).getBackgrounds();
  var count = 0;
  for (var r = 0; r < backgrounds.length; r++) {
    for (var c = 0; c < backgrounds[r].length; c++) {
      if (backgrounds[r][c].toLowerCase() === targetHex.toLowerCase()) {
        count++;
      }
    }
  }
  return count;
}
Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

What getBackgrounds actually returns

getBackgrounds() reads the explicit fill color set on each cell and returns a 2D array of hex strings — one entry per cell in the range. A white, unfilled cell comes back as "#ffffff". That is the complete picture: one hex string per cell, no metadata, no rule names.

The hex strings are always lowercase and always six digits. If you typed "#EA4335" into the fill-color picker, getBackgrounds() hands you "#ea4335". The case-insensitive comparison in the snippet above is defensive but harmless.

The function above is a custom function, meaning you call it directly from a cell formula. Sheets passes the second argument as a Range object, which is why the code calls range.getRow() rather than parsing a string like "A1:A50" itself. That Range-object convention is specific to custom functions — if you call COUNT_BY_COLOR from another script function instead, you would pass a Range object explicitly.

The conditional-formatting trap

This is the thing that bites almost everyone the first time. Conditional formatting can paint a cell red based on a rule — say, any value greater than 100 — and the cell will look red on screen. getBackgrounds() will still return "#ffffff" (or whatever the base fill is). It sees the stored fill, not the rendered color.

The Apps Script API has no method that resolves conditional-formatting rules to their rendered output colors. That rendering happens in the browser, not on the server side where scripts run. If your workflow depends on colors applied by conditional formatting, the only path forward is to replace those rules with a script that sets explicit fills using setBackground(), then count against those stored fills.

I have watched this catch people who built a traffic-light dashboard with CF rules and then tried to count the red cells. The script always returned zero. The fix was a one-time migration: run a script that reads each cell's value, applies the same logic the CF rule used, calls setBackground() with the appropriate hex, and removes the CF rule. After that, COUNT_BY_COLOR works correctly.

Making the formula recalculate when colors change

Custom functions in Sheets recalculate when their input arguments change. Background color is not an argument — it is a side property of the range — so changing a cell's fill does not trigger a recalc. The formula result sits stale until something else forces it.

The standard workaround is to add a dummy volatile argument: a reference to a cell you update manually whenever you repaint cells. Change that cell (increment a counter, update a timestamp), and Sheets recalculates every formula that references it, including COUNT_BY_COLOR.

A heavier alternative is to attach an onEdit trigger that calls SpreadsheetApp.flush() and then writes a timestamp to that dummy cell automatically. That closes the loop so repaints trigger recalcs without manual intervention, at the cost of slightly more script complexity.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions
Why does COUNT_BY_COLOR always return 0 when I use conditional formatting?
getBackgrounds() reads the stored fill color, not the color rendered by a conditional-formatting rule. A cell colored by a CF rule still reports its base fill (usually #ffffff). Replace the CF rule with explicit setBackground() calls and the count will work.
How do I find the exact hex string for a color already in my sheet?
Paste this one-liner into the script editor and run it with a cell selected: Logger.log(SpreadsheetApp.getActiveSheet().getActiveCell().getBackground()). The hex string it logs is exactly what COUNT_BY_COLOR needs to match.
Can I sum values in cells that share a color instead of just counting them?
Yes. Replace the count++ line with count += sheet.getRange(range.getRow() + r, range.getColumn() + c).getValue() and rename the function SUM_BY_COLOR. For large ranges, fetch the values array with getValues() alongside getBackgrounds() and index into it the same way, so you make two API calls instead of one per matching cell.
The formula result does not update after I repaint cells. How do I fix that?
Add a dummy argument to the formula — a reference to a helper cell you bump after repainting, like =COUNT_BY_COLOR("#ea4335", A1:A50, E1). The function ignores E1, but when you change it Sheets marks the formula dirty and recalculates.

Related scripts