How to count colored cells in Google Sheets

Updated November 7, 2026

Google Sheets has COUNTIF for values, but no built-in function that counts cells by their fill color. There are three ways to do it: filter by color and use SUBTOTAL, write a small Apps Script function, or install an add-on that gives you a COUNTBYCOLOR formula. This guide shows all three and when each one fits.

Method 1: Filter by color and SUBTOTAL (no add-on)

Sheets can filter a column by color, and SUBTOTAL counts only the rows the filter leaves visible.

1. Click any cell in your data and choose Data → Create a filter.
2. Click the filter icon in the column header, then Filter by color → Fill color, and pick the color.
3. In a cell above or below the data (not in the filtered rows), type:

   =SUBTOTAL(103, A2:A50)

103 counts non-empty visible cells (like COUNTA). Use 102 to count only numbers.

Good for: a quick one-off count. Limits: one color at a time, empty colored cells are not counted, and the result changes as soon as someone changes the filter. A filter on the sheet also changes the view for every editor; use Data → Filter views if you share the file.

Method 2: Apps Script custom function

You can write your own function. Open Extensions → Apps Script, replace the code with this and click Save:

function countColoredCells(rangeA1, color) {
  var range = SpreadsheetApp.getActiveSpreadsheet().getRange(rangeA1);
  var colors = range.getBackgrounds();
  var target = color.toLowerCase();
  var count = 0;
  for (var r = 0; r < colors.length; r++) {
    for (var c = 0; c < colors[r].length; c++) {
      if (colors[r][c].toLowerCase() === target) count++;
    }
  }
  return count;
}

Then use it in a cell. The range must be in quotes:

=countColoredCells("A2:A50", "#ff0000")

Limits:

• It does not update when a color changes. Sheets recalculates a custom function only when its arguments change, and a color change is not one. The count stays old until you edit the formula.
• The range is text, so it does not move when you insert rows or columns.
• You need the exact color code, like #ff0000. Sheets does not show it next to the cell.
• You have to copy the script into every spreadsheet where you need it.

Method 3: COUNTBYCOLOR with Cellhue

Cellhue is a Google Sheets add-on that adds color formulas. After you install it, count cells with a normal range, the way you use COUNTIF:

=COUNTBYCOLOR(A2:A50, "#ff0000")
=COUNTBYCOLOR(A2:F100, C1)

• The second argument is a color code ("#ff0000", "#f00") or a sample cell like C1 that has the color you want.
• Every cell with that fill color is counted, empty cells included.
• Don't know the color code? Open Extensions → Cellhue → Open sidebar, select the range and click Analyze selection. You see every color in the range with its count, sum and average, and one click inserts the formula.
• Cellhue refreshes its formulas when you open the file and when you click Refresh all. With Pro, it refreshes on its own while the sidebar is open.

To count by text color instead, see count by font color. To add up the numbers in colored cells, see sum by color.

Which method to use

Common questions

Can COUNTIF count by color?

No. COUNTIF and COUNTIFS look at cell values only. Color needs one of the methods above.

Why does my color count not update?

Changing a color is not an edit that makes Sheets recalculate formulas. Custom functions keep the old result until a value in their arguments changes. Cellhue works around this with refresh on open, the Refresh all command and, in Pro, auto-refresh.

Can I count cells colored by conditional formatting?

Not by color: scripts and add-ons read the color set on the cell, not the color a rule displays. Use COUNTIF with the same condition as the rule, for example =COUNTIF(A2:A50, ">100").