COUNTIF to Count Cells by Fill Color in Google Sheets (2 Ways)

When working with color-coded data in Google Sheets, like highlighting overdue tasks in red or completed items in green, you can count how many cells have a specific background color. Unfortunately, Google Sheets does not support counting cells by color using standard COUNTIF formulas.

However, there are effective workarounds. This article guides you through step-by-step methods using Google Apps Script and filter views to count cells based on their background color accurately.

function countByColor(colorRef, range) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const color = sheet.getRange(colorRef).getBackground();
  const values = sheet.getRange(range).getBackgrounds();
  let count = 0;
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      if (values[i][j] === color) {
        count++;
      }
    }
  }
  return count;
}

When analyzing data that’s visually categorized using background colors, such as red for “Urgent” or green for “Completed”, you may want to count how many cells share a specific color. Since Google Sheets doesn’t support counting by color with built-in functions like COUNTIF, you can use a custom Apps Script to create your own countByColor function.

This is the dataset that we will be using to demonstrate the methods:

Steps:

➤ Click Extensions >> Apps Script in your Google Sheet

➤ Delete any existing code and paste this:

function countByColor(colorRef, range) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const color = sheet.getRange(colorRef).getBackground();
  const values = sheet.getRange(range).getBackgrounds();
  let count = 0;
  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      if (values[i][j] === color) {
        count++;
      }
    }
  }
  return count;
}

➤ Save the script by clicking on the floppy disk icon.

➤ Use this formula in your sheet:

B4 should contain the background color you want to count. In this case, we are trying to do a count of the yellow colored cells that highlight tasks that are pending

This approach gives you a live count of color-coded cells, updating automatically as your data changes.

If you can’t use Google Apps Script or want a simpler, no-code solution, this method is ideal. By manually labeling each row with its color meaning (e.g., “Red” for urgent tasks), you can use standard formulas like COUNTIF to count color-coded categories. This is especially useful for shared sheets where script access isn’t available or when colors follow a clear, agreed meaning.

Steps:

➤ Add a new column C next to your dataset and label it Color Tag.
➤ Manually enter color labels in column C that match the cell’s background in column B (e.g., type Red for all “Urgent” rows, Green for “Done”, etc.)

➤ To count how many tasks are marked “Red” (Urgent), use this formula:

➤ Press Enter.

This will return the number of rows labeled as “Red”, matching your color-coded urgency level.

Frequently Asked Questions

Can Google Sheets count cells by background color directly?

No, Google Sheets doesn’t support counting by color natively. You’ll need to use Apps Script or a helper column to do it manually.

What’s the easiest way to count colored cells without a script?

Use a helper column where users manually label each row’s color meaning. Then apply COUNTIF to that column to count based on the labels.

Does the color-counting script update automatically?

Not always. The script runs when recalculated, but changes in cell color alone don’t trigger it. Editing a formula or cell may be needed to refresh the count.

Can I use conditional formatting colors with the script?

No, the getBackground() method doesn’t detect conditional formatting colors. It only works with manually applied background colors in the cell formatting.

Wrapping Up

Counting cells by color in Google Sheets isn’t built-in, but it’s still achievable with the right approach. If your data updates frequently or you want dynamic tracking, the custom Apps Script method is the most efficient. For simpler setups or when scripts aren’t an option, using a helper column provides a practical alternative. Whether you’re managing task statuses, priority flags, or visual labels, these methods let you turn cell colors into actionable insights.