Google Sheets: Count Cells by Highlight Color

Google Sheets does not have a built-in COUNTIF function that counts cells just because they are highlighted with a fill color. To count them, use a filter and SUBTOTAL for a quick check, count the rule that creates conditional formatting, or use a helper column or Apps Script when you need a repeatable count of manually colored cells.

What “highlighted” means in Sheets

A highlighted cell may have a fill color applied directly with the Fill color button, or it may look colored because a conditional formatting rule is currently true. Those two cases matter: direct formatting is an appearance choice, while conditional formatting is usually the visible result of a rule based on the cell’s value or another condition.

Sheets formulas work with cell values, not ordinary formatting. A cell colored yellow by hand does not contain a “yellow” value that COUNTIF can test. Nor can a formula reliably infer why a cell has a color: the same shade might be used for different reasons, and a rule can change the appearance without changing the underlying value.

Before choosing a method, decide what you really need to count:

Cells currently showing a particular color: use a filter or a script that reads backgrounds.
Rows that meet the condition behind a color: count that condition with a normal formula.
Records assigned a category: use a category or status column, then count that value.

Why COUNTIF cannot count a fill color

COUNTIF checks whether cell contents match a criterion. For example, with numbers in A2:A100, the formula =COUNTIF(A2:A100,">100") counts values greater than 100. It does not check the cells’ background color. There is no standard criterion such as “yellow fill” that you can add to COUNTIF, and changing a cell’s fill does not make a value-based formula recalculate as though its contents had changed.

This is a common source of confusion because a highlighted value can appear to be the thing being counted. If conditional formatting colors values over 100, counting the numbers over 100 will usually give the relevant answer, but the formula is counting the values—not inspecting their displayed color. That distinction matters if someone later changes the formatting rule or manually colors cells that do not meet it.

If the color comes from conditional formatting

When a color is created by a conditional formatting rule, count the rule’s underlying condition whenever possible. In the browser, select the relevant cells and open Format > Conditional formatting to review the rule. For example, if the rule colors numbers greater than 100 in A2:A100, count values that meet that same threshold rather than trying to count the color.

With the numbers in A2:A100 and the rule set to greater than 100, =COUNTIF(A2:A100,">100") returns the number of cells meeting the condition. If the rule checks text, dates, or a custom formula, make the counting formula follow the same logic. For a rule based on another column, count the corresponding values in that column or use a suitable COUNTIF or COUNTIFS formula.

Check the rule carefully before relying on this approach. A conditional formatting rule can apply to a range but use a relative reference, and multiple rules may overlap. A manually colored cell may also look the same as a rule-colored cell while failing the rule. Counting the condition is appropriate when the question is “How many records meet this rule?” It is not a guaranteed count of every cell that currently looks colored.

Quick check: filter by fill color

For a one-time count of records, filtering by color can be faster than creating a script. Select the data range, choose Data > Create a filter, open the filter menu for the column whose cells are colored, then choose Filter by color > Fill color and select the shade you want. The filter hides rows that do not match; it does not delete them.

To count the visible, non-empty records after filtering, use SUBTOTAL on a column that has one nonblank value in every record, such as an ID column. With IDs in A2:A100, place this formula outside the filtered range: =SUBTOTAL(103,A2:A100). Function number 103 counts non-empty visible cells and excludes rows hidden by a filter. If some visible records have blank IDs, the result will be lower than the number of visible rows. This method also cannot count highlighted blank cells, because there is no non-empty record to count in that column.

Filtering is useful when you need to inspect matching rows as well as get a count. It is less suitable for a permanent dashboard total: the result depends on the current filter, and another person may change or remove that filter. Be sure you filter the column that actually carries the color; a row’s color in one column does not necessarily indicate that another column has the same fill.

Use a helper column for a dependable count

If highlighting represents a meaningful status—such as “Needs review,” “Paid,” or “Overdue”—store that status in a column instead of relying on color alone. Then use conditional formatting to color cells based on the status. This makes the reason for the highlight visible, searchable, accessible to people who cannot distinguish the color, and straightforward to count.

For example, put status labels in B2:B100 and use a rule to color cells labeled “Needs review.” With statuses in B2:B100, =COUNTIF(B2:B100,"Needs review") returns the number of records in that category. If status depends on more than one field, a COUNTIFS formula can count records that meet several criteria. The key is to make the text or values in the sheet the source of truth, and treat fill color as a visual cue.

A helper column is also a good choice when people currently apply colors by hand but can agree on a small set of labels. Add a Status column, fill it for existing records, and update the color rule to follow it. This takes some initial cleanup, but avoids totals silently becoming inaccurate when someone changes a fill, pastes formatting, or uses a slightly different shade.

Count manually applied colors with Apps Script

If the color itself is the information you need to count, Apps Script can read cell backgrounds. In the spreadsheet, open Extensions > Apps Script, add the function below, and save the project. It defines a custom function that counts cells whose background matches a supplied hex color.

function COUNT_BY_COLOR(sheetName, a1Range, hexColor, refreshRange) {
  // Read backgrounds from the requested range and compare color codes.
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  if (!sheet) throw new Error('Sheet not found: ' + sheetName);

  const target = String(hexColor).toLowerCase();
  const backgrounds = sheet.getRange(a1Range).getBackgrounds();
  let count = 0;

  for (const row of backgrounds) {
    for (const color of row) {
      if (color.toLowerCase() === target) count++;
    }
  }
  return count;
}

For a tab named Sheet1 with cells A2:A100 to check, enter =COUNT_BY_COLOR("Sheet1","A2:A100","#ffff00",A2:A100) in a cell outside that range. Replace the color code with the shade used in your sheet. The fourth argument passes the range as a dependency so changes to its cell values can trigger recalculation; the function uses the range address in its second argument to read the backgrounds.

There is an important limitation: changing only a cell’s formatting may not cause a custom function to recalculate. After changing fills, edit and re-enter the formula or otherwise trigger recalculation, then check the result. Custom functions also add script maintenance to the spreadsheet, so consider whether a status column would be simpler for a shared or business-critical file. Apps Script reads the specified range’s background colors; it does not determine which conditional formatting rule caused a displayed color.

Common counting mistakes

Counting values instead of colored cells: COUNTIF gives the right answer only if its criteria match the meaning of the highlight.
Mixing manual and conditional colors: visually identical cells can have different origins. Decide whether the count should reflect the rule or every matching background.
Counting only nonblank rows: SUBTOTAL with function number 103 counts non-empty visible cells, not empty colored cells.
Using inconsistent shades: a filter or script targeting one fill color may not include cells colored with a nearby but different shade.
Expecting a fill change to update a formula: formatting is not a value edit, so formula-based counts and custom functions may need a refresh.

For spreadsheets used by several people, document the meaning of each color and avoid using color as the only indicator. A text label plus conditional formatting gives people a readable category and a countable value, while reducing errors caused by inconsistent manual formatting.

Frequently Asked Questions

Can I use COUNTIF to count yellow cells in Google Sheets?
No. COUNTIF checks cell values, not fill color. Count the condition that causes the yellow color, use a filter for a quick visible-record count, or use Apps Script to inspect backgrounds.

Can Google Sheets count cells highlighted manually?
Not with a built-in spreadsheet formula. You can filter by fill color for a one-time check, or use a custom Apps Script function to compare background colors. A script-based count may need refreshing after formatting changes.

How do I count cells colored by conditional formatting?
Usually, count the values or records that meet the conditional formatting rule. Open Format > Conditional formatting to check what the rule tests, then write a formula that uses the same condition.

Does SUBTOTAL count filtered rows by color?
SUBTOTAL can count visible non-empty cells after you filter by color. For example, function number 103 excludes filtered-out rows, but it does not count blank cells and is only a count of records if the chosen column is populated for every record.

Will a color-count formula update when I change a cell’s fill?
Not reliably. A fill change is a formatting change, not a value change. Re-enter a custom function formula after changing formatting, or use a value-based status column so ordinary formulas can update from cell contents.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.