Google Sheets COUNTIF by Color: Practical Ways to Count

Google Sheets’ COUNTIF cannot count cells by fill color directly: it evaluates cell values against a criterion, not formatting. To count colored cells, filter by color for a quick check, count a data category that the color represents, or use Apps Script when you need to inspect fills themselves.

What COUNTIF Can—and Cannot—Check

COUNTIF is designed to test a range for a condition such as a matching word, number, or comparison. A fill color is formatting rather than cell content, so there is no built-in color criterion to put in the function. Typing a color name, a hex code, or a cell reference containing a colored fill as the criterion will not make the function inspect that formatting.

For example, with task statuses in B2:B100, the formula =COUNTIF(B2:B100,"Done") counts cells whose value is Done. It does not count green cells. If green is being used to mark Done tasks, this formula gives the right answer only when the underlying status values are also Done; changing a cell’s fill alone does not affect the result.

This distinction matters because two cells can look alike but contain different values, and two cells with the same value can have different fills. A formula can reliably count the value, but the visual style may have been applied manually, by conditional formatting, or by a theme. Those are separate things in a spreadsheet.

Best Long-Term Method: Count the Meaning, Not the Color

If a color represents a category—such as Approved, Needs review, or Overdue—store that category in a cell and use formatting to make it easy to see. This keeps the meaning available to formulas, filters, screen readers, and collaborators who may not distinguish colors clearly. It also avoids a count becoming wrong when someone changes a fill or applies a new theme.

For example, put each task’s status in column B and apply conditional formatting so Done appears green and Needs review appears yellow. With statuses in B2:B100, =COUNTIF(B2:B100,"Needs review") counts the review tasks. The formula result is based on the text, so it remains correct if you later change yellow to blue.

A useful data layout might include:

Status: a consistent value such as Done, In progress, or Needs review.
Formatting: a visual cue tied to the status, rather than a substitute for it.
Summary: formulas that count the status values for a dashboard or report.

When creating or revising a sheet, this is usually the most dependable approach. It is especially helpful for shared trackers, recurring reports, and sheets where counts need to update automatically when rows change.

Quick Check: Filter the Sheet by Fill Color

If you only need a one-time count, Google Sheets can filter a range by its displayed fill color. This is often quicker than adding a script. Select the data range, choose Data > Create a filter, and open the filter dropdown for the relevant column. Choose Filter by color > Fill color, then select the color you want to inspect.

Once the filter hides rows with other fills, you can count nonblank cells in a suitable column. With records in rows 2 through 100 and nonblank IDs in A2:A100, enter =SUBTOTAL(103,A2:A100) in a cell outside the filtered range. The 103 option counts nonempty cells while ignoring rows hidden by a filter. If four visible records have IDs, the result is 4.

There are limits to this shortcut. It counts visible nonblank entries in the chosen column, not colored cells in the abstract. If a matching colored cell has a blank ID, it will not contribute to that result. Choose a column that is filled for every record you intend to count. Also, filtering changes what is visible to anyone looking at that sheet, so clear the filter when finished.

Count the Fill Itself with Apps Script

For a count based on manually applied background fills, Apps Script can read the backgrounds in a range and compare them with a sample cell’s background. The script below adds a menu item. When you run it, it asks for a range to count and a sample cell whose fill defines the target color; both must be on the active sheet. The result appears in an alert rather than recalculating as a sheet formula.

Open the spreadsheet and choose Extensions > Apps Script.
Replace any starter code with the script below, then save the project.
Return to the spreadsheet and reload it. The Color tools menu should appear.
Choose Color tools > Count matching fills, enter a range such as A2:A100, and then enter a sample cell such as D1.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Color tools")
    .addItem("Count matching fills", "countMatchingFills")
    .addToUi();
}

function countMatchingFills() {
  const ui = SpreadsheetApp.getUi();
  const sheet = SpreadsheetApp.getActiveSheet();
  const rangeAnswer = ui.prompt(
    "Count matching fills",
    "Enter the range to count, such as A2:A100:",
    ui.ButtonSet.OK_CANCEL
  );
  if (rangeAnswer.getSelectedButton() !== ui.Button.OK) return;

  const sampleAnswer = ui.prompt(
    "Choose the fill color",
    "Enter a sample cell on this sheet, such as D1:",
    ui.ButtonSet.OK_CANCEL
  );
  if (sampleAnswer.getSelectedButton() !== ui.Button.OK) return;

  const cells = sheet.getRange(rangeAnswer.getResponseText().trim());
  const sample = sheet.getRange(sampleAnswer.getResponseText().trim());
  const targetColor = sample.getBackground();
  const backgrounds = cells.getBackgrounds();
  let count = 0;

  backgrounds.forEach(function(row) {
    row.forEach(function(color) {
      if (color === targetColor) count++;
    });
  });

  ui.alert("Cells with the sample fill: " + count);
}

The script counts every cell in the requested range whose background matches the sample cell, including blank cells. If you want to count only filled cells with values, that is a different rule and the script needs an additional value check. The comparison is for background fill, not text color, borders, or the color of a chart element.

On first use, Google may ask you to authorize the script. Check that you understand the permissions before approving. This is a menu action, not a live spreadsheet function: run it again after changing fills to get an updated count. Apps Script’s background-reading methods may not represent the color a conditional-formatting rule displays, so verify that case separately rather than assuming the script sees the visible result.

Manual Fills and Conditional Formatting Are Different

A manually applied fill is set through the toolbar’s fill color control. Conditional formatting, by contrast, displays a style when a rule is true. A cell may look green because its value meets a rule even though its ordinary background formatting is not green. That is why a method that reads a cell’s stored background may not answer a question about the color currently shown by a rule.

If the color comes from a rule, the cleanest count is usually to count the condition behind that rule. For instance, if a conditional format makes dates in C2:C100 red when they are past due, count dates against the current date instead of trying to count red fills. This makes the count respond to the underlying data and avoids relying on appearance. Make sure the counting condition matches the conditional-format rule exactly, including blank-cell handling and any additional conditions.

To inspect a rule, select the relevant cells and choose Format > Conditional formatting. Review the rules and their ranges. If several rules can apply to a cell, determine which condition is intended to define the count; the visible style alone may not tell you which rule caused it.

Choosing the Right Approach

The right option depends on why the cells are colored and whether the result needs to stay current. A quick filter is convenient for a spot check, while a value-based design is more reliable for ongoing reporting. A script is useful when the fill itself is genuinely the data and changing the sheet structure is not practical.

Choose COUNTIF on a helper value when color represents a status or category and you want an automatic summary.
Choose Filter by color when you need to inspect or count a set of manually colored records once.
Choose Apps Script when you need to compare actual background fills across a range and accept a manual rerun.
Rework the design when colors are being used inconsistently or when a report must be auditable and dependable.

There is no direct spreadsheet formula that turns a fill into a normal COUNTIF condition. A custom function is not automatically a better answer: custom functions have restrictions, and formatting changes do not necessarily trigger a formula recalculation. A script run from a menu makes the operation explicit, while a status column makes the information available to ordinary formulas.

Common Problems and Misconceptions

Counting the wrong column: after filtering by color, SUBTOTAL counts nonblank cells in the range you give it. Use a column populated for every record, not a column that may be blank.
Expecting blank colored cells to count with SUBTOTAL: function number 103 counts nonempty visible cells. Use a script or a different data design if blank cells themselves must be counted by fill.
Assuming a displayed color is a manual fill: conditional formatting can create the appearance. Count the rule’s condition when possible.
Using color names as criteria: a cell containing the word Green is countable as text; a cell merely filled green is not.
Expecting an immediate update: changing a fill does not make COUNTIF recalculate because COUNTIF does not depend on that formatting. Rerun a menu script or use a value-based formula.

Google Sheets and Excel share the central limitation: their standard COUNTIF functions test cell contents, not background color. The menus and automation options differ between the programs, so instructions or scripts for one should not be copied into the other unchanged.

Frequently Asked Questions

Can COUNTIF count cells with a specific background color in Google Sheets?
No. COUNTIF checks values against a criterion and has no built-in fill-color criterion. Use a value that represents the category, filter by color for a manual check, or use Apps Script to compare backgrounds.

Can I count cells by color without Apps Script?
Yes, for a quick check, create a filter and use Filter by color. If the color represents a meaningful category, a status column with COUNTIF is usually better and updates automatically.

Does COUNTIF work with colors from conditional formatting?
No. COUNTIF does not inspect the displayed style. Count the value condition that triggers the conditional-formatting rule instead; this is generally more reliable than trying to count the resulting appearance.

Will a script count blank cells that have the chosen fill?
The example script counts matching backgrounds whether the cells are blank or not. The SUBTOTAL example counts only nonblank visible cells, so those two methods can return different totals.

Does changing a cell’s color update a formula result?
Not for COUNTIF, because its calculation does not depend on fill formatting. A menu-based script must be run again after a color change, while a formula counting the underlying status will update when that status value changes.

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.