Count Colored Cells in Google Sheets (3 Ways – Full Guide)

Here’s how to count colored cells in Google Sheets. I have a full video guide and a written guide with step-by-step instructions and screenshots below.

Does Google Sheets Have a Formula for Cell Color?

The short answer is no. Unlike Excel, Google Sheets does not have a native “COUNTIF” function that works based on background cell color. To achieve this, you must use one of three workarounds: a custom script (free), a manual filter (no-code), or a third-party add-on.

Use Case: Counting Colored Cells in a Spreadsheet

The more I use Google Sheets, the more I realize how powerful it is. However, I often wish there was an inbuilt way to count colored cells.

For example, suppose I have a data set as shown below, and I want to quickly know the total number of green cells and cells with a yellow background color.

Since this functionality is not native, we will create a custom function using Google Apps Script. You can make a copy of our EXAMPLE SPREADSHEET and follow along.

Video Guide

Method 1: Use a Custom Function (Apps Script)

The most flexible way to count colored cells is to create a custom function using Google Apps Script. Don’t worry. You don’t need to be a coder. You just need to copy and paste.

Below is the script we will use:

function countColoredCells(countRange,colorRef) {
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  
  var rangeA1Notation = formula.match(/\((.*)\,/).pop();
  var range = activeSheet.getRange(rangeA1Notation);
  var bg = range.getBackgrounds();
  var values = range.getValues();
  
  var colorCellA1Notation = formula.match(/\,(.*)\)/).pop();
  var colorCell = activeSheet.getRange(colorCellA1Notation);
  var color = colorCell.getBackground();
  
  var count = 0;
  
  for(var i=0;i<bg.length;i++)
    for(var j=0;j<bg[0].length;j++)
      if( bg[i][j] == color )
        count=count+1;
  return count;
};

Step-by-Step Instructions

1. In your Google Sheets document, click on Extensions in the top menu.
2. Select Apps Script. This will open the Apps Script Editor in a new tab.
   

   

3. By default, you will be in the Code.gs window. Delete any code that is already there.
4. Copy and paste the code block provided above into the editor.
   

   

5. Click the Save icon (disk) in the toolbar.
   

   

6. Close the Apps Script window.

How to Use the Formula

Now that the code is saved, you can use the custom formula just like any other Google Sheets function. This will help you automatically sort color-coded data in your dataset.

=countcoloredcells(range, cell_with_color)

Example: To count all the green names in the range A2:A11, assuming cell C1 is also green:

=countcoloredcells(A2:A11,C1)

The formula checks every cell in the range (A2:A11). If the background color matches the reference cell (C1), it counts it.

How Does This Formula Work?

This custom formula takes 2 arguments:

• The range of cells that you want to count.
• The reference cell that has the background color you are looking for.

The script uses the getBackground() function to retrieve the Hex code of the reference cell. It then loops through every cell in your selected range. If the Hex code of a cell in the range matches the reference cell, it adds +1 to the count.

How to Make the Formula Automatically Update

There is one limitation: Changing a cell’s color does not trigger a formula recalculation.

If you change a cell from white to green, the count will not update immediately. Here is the fix:

The “Space” Trick: Go to any cell in the range, enter edit mode (press F2), add a space at the end, and hit Enter. This forces the sheet to recalculate. You can also just delete the formula and undo the deletion to force a refresh.

Method 2: Filter + SUBTOTAL (No Code)

If you aren’t comfortable with scripts, you can use the built-in Filter tool combined with the SUBTOTAL function.

1. Select your data range.
2. Go to Data > Create a Filter.
   

   Create a Filter.”/>

3. Click the filter icon on the header row. Select Filter by color > Fill Color and choose the color you want to count (e.g., Yellow).
   

   

4. In an empty cell, use our subtotal formula: =SUBTOTAL(103, A2:A11).
   

   

Why this works: The code 103 in the SUBTOTAL function tells Google Sheets to act like COUNTA, but only for visible rows. Since you filtered out everything except the yellow rows, it returns the correct count.

Method 3: Using an Add-On (Function by Color)

There are third-party tools called Add-ons that can do this for you. One of the most popular is “Function by Color” by Ablebits.

How to Install and Use

1. Go to Extensions > Add-ons > Get add-ons.
   

   Add-ons. Click Get Add-ons.”/>

2. Search for “Function by Color” and install it.
   

   

3. Once installed, go to Extensions > Function by Color > Start.
4. A sidebar will appear. Select your range, pick the background color you want to count, and select “COUNTA” as the function.
   

   

5. Select where you want the result to appear and click Insert Function.
   

   

Bonus: How to Sum Values by Color

You can use the same Add-on method above to Sum values instead of counting them. This is great for expense sheets where you highlight “Paid” items in green and want to sum the total paid amount.

• Follow the Add-on steps above.
• Instead of selecting “COUNTA” in the dropdown, select SUM.
• Select the cell where you want the total to appear and click “Insert Function.”

Frequently Asked Questions

Why doesn’t my color count update when I change a cell color?
Google Sheets formulas only recalculate when the data inside a cell changes, not the formatting. To force an update, edit any cell or press F2 + Enter on the formula cell.

Can I count cells with conditional formatting?
No, the script method above only counts manually applied colors. Conditional formatting does not actually change the cell’s background attribute in the same way, so getBackground() often fails to see it.

Is there a way to sort by color?
Yes! You don’t need a formula for this. Simply create a Filter (Data > Create a Filter), click the filter icon on the header, and choose “Sort by Color.”

Wrapping Up

In this article, we have shown you 3 ways to count colored cells in Google Sheets. You can use a custom formula in Google Sheets (Script) or the function by color add-on. You can also use the SUBTOTAL function as a countif by color formula by applying a filter first.

I hope you found this tutorial useful. You can also check out how to count cells if not blank in Google Sheets.