Google Sheets COUNTIF by Cell Color: What Actually Works

No—Google Sheets’ COUNTIF function cannot count cells by their fill color. It checks cell values against a criterion, not formatting; to count color reliably, count the underlying values or use a manual Apps Script procedure for cells whose color was applied directly.

Why COUNTIF Cannot Read Cell Color

A cell’s displayed fill is formatting, separate from its contents. A cell might contain the text “Approved,” a date, a number, or nothing at all, while its background is set independently through the toolbar or a conditional formatting rule. COUNTIF evaluates the contents of a range against a condition; it does not examine visual properties such as fill color, font color, bold text, or borders.

That distinction explains why a formula such as =COUNTIF(A2:A20,"green") counts cells containing the word “green,” not cells with a green background. There is no built-in Google Sheets function called COUNTBYCOLOR or a color criterion you can add to COUNTIF. A custom formula can be built with Apps Script, but it has important refresh and formatting limitations, so it is not always the best answer.

Choose a Method Based on What the Color Means

Before counting colored cells, decide whether color is merely decoration or whether it represents a meaningful category. If green means “paid,” yellow means “waiting,” or red means “overdue,” the most dependable approach is to count the status that caused the color. Color is a useful visual cue, but it is a fragile source of data: someone can change a fill without changing the underlying status, and a colored cell does not explain why it was colored.

Color reflects a value or status: Count that value with COUNTIF or COUNTIFS. This is usually the simplest and most maintainable choice.
You need to inspect a set of manually colored cells once: Use a filter by color or a small Apps Script procedure.
The color comes from conditional formatting: Count the rule’s condition rather than trying to count the resulting appearance.
People regularly assign colors by hand: Consider adding a status column or a dropdown so the category is recorded explicitly.

Count the Underlying Status with COUNTIF

If colors are assigned to show categories already recorded in the sheet, count those categories directly. For example, suppose column C contains a status and conditional formatting makes “Approved” cells green. Counting the status gives a live result and avoids relying on the fill itself.

With statuses in C2:C100, this formula counts rows marked Approved:

=COUNTIF(C2:C100,"Approved")

It returns the number of cells in that range whose value matches “Approved.” If the status is stored in a separate cell—for example, F1—use that cell as the criterion:

=COUNTIF(C2:C100,F1)

This is useful when a sheet has several categories. Put status names in F1:F3 and copy the formula down beside them, adjusting the criterion reference as needed. The results then count categories, not the colors used to display them. If a row must meet multiple conditions, such as an Approved status and a particular team, use COUNTIFS with a criterion for each relevant range.

Count Conditional-Formatting Colors by Their Rule

Conditional formatting changes how cells look when a condition is true. For example, a rule might apply a green fill to cells containing “Paid.” The rule’s condition—not the displayed green—is the meaningful test. Count cells meeting that same condition with a formula. If a rule depends on a number, date, or combination of columns, reproduce that logic in an appropriate counting formula.

To review the rule, select the affected cells and choose Format > Conditional formatting. The conditional-formatting panel shows the range and the condition used. If the rule says “Text is exactly” Paid, counting Paid values is more robust than trying to inspect the visual result. If it uses a custom formula, make sure the counting logic matches the rule, including any relative references and additional conditions.

A common misconception is that a cell that looks green must have a green fill stored as its ordinary formatting. With conditional formatting, the color may be applied only while a rule is true. The underlying fill setting and the visible appearance can differ. For this reason, scripts that inspect basic background formatting may not give a dependable count of colors produced by conditional formatting.

Use Filter by Color for a Quick Manual Check

For a one-time visual review, Google Sheets can filter a range by fill color. Select the data and choose Data > Create a filter. Use the filter icon in the relevant column, choose Filter by color, then select a fill color. The sheet hides rows that do not match the selected color, making the matching records easier to inspect.

This is convenient when you need to find or review colored records, but it is not a replacement for a count formula. Filtering changes which rows are shown; it does not create a durable count-by-color calculation. Check the filtered rows or selected cells for the number you need, and remember that filtering a range can affect what collaborators see. Remove the filter when finished, or use a temporary filter view if you want to avoid changing the shared view for other users.

Count Manually Applied Fill Colors with Apps Script

If cells are colored manually and you need a repeatable count, an Apps Script can compare their background colors. The example below counts cells in the currently selected range that match the fill color in D1, then writes the result to E1. It is intended for direct fill formatting, not as a dependable way to count conditional-formatting results.

Put a sample of the color to count in D1. Keep D1 and E1 outside the range you plan to count.
Open Extensions > Apps Script. Add the following code in the script editor and save the project.
Return to the sheet, select the cells to check, and reload the spreadsheet so the custom menu appears.
Choose Color count > Count selected cells. On the first run, Google will ask you to authorize the script. The count will be written to E1.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Color count')
    .addItem('Count selected cells', 'countSelectedCells')
    .addToUi();
}

function countSelectedCells() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getActiveRange();
  const targetColor = sheet.getRange('D1').getBackground();
  const backgrounds = range.getBackgrounds();
  let count = 0;

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

  sheet.getRange('E1').setValue(count);
}

This script runs when you choose the menu item; it is not a live spreadsheet formula. Run it again after changing colors or the selected range. It counts every matching cell in the selection, including blanks that have the target fill. If blank cells should not count, the script needs an additional check of cell values. For repeated reporting, a status column is easier to maintain than a script whose result must be refreshed manually.

Common Problems and Limitations

The formula counts the word, not the fill: A criterion such as “green” searches for cell content. It does not test background color.
The visible color comes from a rule: A basic background-formatting check may not reflect a conditional-formatting display. Count the rule’s condition instead.
The color changed but the result did not: A manually run script does not recalculate itself. Run the menu command again after formatting changes.
The count includes empty colored cells: A color-only count treats matching blank cells as matches. Limit the selection or add a value check if blanks should be excluded.
Two colors look alike but differ: Similar shades can be distinct color values. Use a sample cell for the exact fill you intend to count.
The script cannot be run: Check that the project was saved, the sheet was reloaded to add the menu, and the requested authorization was completed.

Google Sheets and Excel: The Same Core Limitation

Google Sheets and Microsoft Excel both distinguish cell values from formatting. Their standard counting functions count values according to criteria; neither ordinary COUNTIF nor Excel’s equivalent can use a fill color as its criterion. Both programs offer ways to filter or inspect by color, and both can be extended with code, but those approaches have different setup and refresh behavior.

Instructions for Excel’s ribbon or VBA do not apply directly to Google Sheets. In Sheets, use its own menus and Apps Script, as described above. If a spreadsheet is being shared or converted between the programs, a status-based count is especially portable: formulas and conditional formatting based on the same underlying values are easier to maintain than a color-counting script.

Frequently Asked Questions

Can I use COUNTIF to count green cells in Google Sheets?
No. COUNTIF checks cell contents, not fill color. Count the value that causes the green formatting, use a filter for a quick review, or use Apps Script for manually applied fills.

Is there a built-in Google Sheets formula for counting cell colors?
No. Google Sheets does not provide a standard worksheet function that counts cells by fill color. Apps Script can inspect direct formatting, but it is not a built-in formula and may need to be run again after colors change.

Can a custom formula count conditional-formatting colors?
It is better to count the condition that triggers the rule. Conditional formatting controls the displayed appearance, and reading basic background formatting is not a reliable substitute for evaluating the rule.

Will a color count update automatically after I change a fill?
A script run from a custom menu updates only when you run it. A function that relies on formatting also cannot be assumed to recalculate whenever a color changes. For a live result, count a status or value instead.

What is the best alternative to counting colors?
Store the category in a status column, then apply conditional formatting to that status and count it with COUNTIF. This keeps the data, the visual cue, and the result connected.

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.