How to Count Colored Cells in Google Sheets (3 Methods)

Google Sheets has no built-in formula for counting cells by background color. To count highlighted or color-coded cells, you need one of three workarounds: a free custom script, a no-code filter method, or a third-party add-on.

Quick Summary: 3 Ways to Count by Color

You can make a copy of our example spreadsheet and follow along with any of the methods below.

Use Case: Counting Color-Coded Cells

Say you have a dataset where names or tasks are highlighted in green or yellow, and you want a live count of each color. That is a common setup for tracking status, priority, or categories. Since Google Sheets does not handle this natively, you need a workaround.

Video Guide

Method 1: Custom Function via Apps Script (Recommended)

The most flexible way to count colored cells is a custom function built with Google Apps Script. You do not need to know how to code. Just copy and paste the script below.

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 Extensions in the top menu.
2. Select Apps Script. This opens the Script Editor in a new tab.
   

   

3. In the Code.gs window, delete any existing code.
4. Paste the script above into the editor.
   

   

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

   

6. Close the Apps Script tab and return to your spreadsheet.

Note on authorization: The first time you use this function in a cell, Google will ask you to authorize the script. Click Allow. This is a one-time step and is required for the script to read cell background colors.

How to Use the Formula

Once saved, you can use the custom function just like any native Google Sheets formula:

=countcoloredcells(range, cell_with_color)

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

=countcoloredcells(A2:A11,C1)

The formula checks every cell in the range. If the background color matches the reference cell, it adds 1 to the count.

How the Formula Works

The function takes two arguments:

The script uses getBackground() to retrieve the hex color code of the reference cell, then loops through every cell in the range using getBackgrounds(). When a hex code matches, it increments the count. The difference between the two: getBackground() reads one cell; getBackgrounds() reads an entire range at once, which is more efficient for looping.

How to Force the Formula to Refresh

Changing a cell’s color does not trigger a formula recalculation in Google Sheets. Formulas only recalculate when data inside a cell changes, not when formatting changes.

The Force-Refresh Trick: Click any cell in the range, press F2 to enter edit mode, add a space, and press Enter. This forces the sheet to recalculate and your color count will update. You can also delete the formula cell and immediately undo (Ctrl+Z) to trigger a refresh.

Troubleshooting: Why Is the Count Returning 0?

If your formula returns 0 when you expect a higher number, check these common causes:

• The reference cell color does not match exactly. Two cells that look the same shade of green may have different hex codes. Click both cells and compare their fill colors using Format > Text & Borders.
• You are trying to count conditional formatting colors. This script only detects manually applied background colors. Colors applied through conditional formatting rules are not visible to getBackground(). See the FAQ below for more on this.
• The script was not authorized. If you skipped the authorization prompt, the function will fail silently. Go to Extensions > Apps Script and run the function once manually to trigger the authorization flow.

Method 2: Filter + SUBTOTAL (No Code)

If you prefer not to use a script, the built-in Filter tool combined with the SUBTOTAL function gets the job done without any code.

1. Select your data range.
2. Go to Data > 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 (for example, yellow).
   

   

4. In an empty cell, enter the SUBTOTAL formula:

   =SUBTOTAL(103, A2:A11)

   

The function code 103 tells SUBTOTAL to behave like COUNTA but only for visible rows. Since the filter is hiding every row that does not match your selected color, the result is an accurate count of that color only.

Limitation: You can only count one color at a time. To see multiple counts, you need to change the filter or switch to the Apps Script method.

Method 3: Add-On (Function by Color)

Third-party add-ons handle color counting through a sidebar UI with no coding required. The most popular option is “Function by Color” by Ablebits. Note that as of 2025, this add-on requires a paid subscription for full functionality. For a free alternative, the Apps Script method above is the better long-term choice. You can also browse other options at our roundup of the best Google Sheets add-ons.

How to Install and Use

1. Go to Extensions > Add-ons > 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 open. Select your range, choose the background color you want to count, and select COUNTA as the function.
   

   

5. Select the output cell and click Insert Function.
   

   

Bonus: How to Sum Values by Color

You can use either the Apps Script method or the add-on to sum values in colored cells instead of counting them. This is useful for expense trackers where “paid” invoices are highlighted in green and you want a running total.

To use the add-on for summing: follow the steps above but select SUM from the function dropdown instead of COUNTA.

To use Apps Script for summing: duplicate the countColoredCells function, rename it sumColoredCells, and replace count=count+1 with count=count+values[i][j]. The logic is identical, but it adds the cell value instead of incrementing a counter.

Which Method Should You Use?

Use the Filter + SUBTOTAL method if you need a quick one-off count and do not want to touch any code. Use the Apps Script method if you want a persistent formula that lives in a cell and can be referenced elsewhere in your sheet. Use an add-on if your team is non-technical and needs a point-and-click interface, and you are comfortable with a paid tool.

You can also grab a copy of our example spreadsheet with the Apps Script already installed and ready to use.

Wrapping Up

Google Sheets does not count colored cells natively, but the three methods above cover every use case. The Apps Script approach is the most powerful for ongoing use, the Filter + SUBTOTAL method requires no setup for a quick one-time count, and add-ons are the best fit for non-technical users who want a point-and-click solution.

For more counting techniques, check out our guide on how to count cells if not blank in Google Sheets.