How to Count Colored Cells in Google Sheets

One thing you may want to know how to do in Google Sheets is to count the number of cells that are colored or highlighted.

Whether it’s cells with conditional formatting or just cells highlighted with a background color for certain cells, counting cells with colors can sometimes be useful.

Unfortunately, there is not a good built-in way to do this in Google Sheets automatically. But there are two methods we can use to accomplish this.

In this tutorial, I will show you how to count colored cells in Google Sheets.

Table of Contents

Count Colored Cells with Custom Script

Since there is not a built-in way in Google Sheets to count colored cells, we will have to create a custom Google script and create a function that will do this.

Here are the steps to do this:

1. In the top menu select Tools>Script editor

2. In the Code.gs window, delete anything that already exists and paste this code in the box:

function COUNTCOLORED(countRange,colorRef) {

  var activeRange = SpreadsheetApp.getActiveRange();

  var activeSheet = activeRange.getSheet();

  var activeformula = activeRange.getFormula();

  var rangelocation = activeformula.match(/\((.*)\,/).pop();

  var range = activeSheet.getRange(rangelocation);

  var bg = range.getBackgrounds();

  var values = range.getValues();

  var colorCellLocation = activeformula.match(/\,(.*)\)/).pop();

  var colorCell = activeSheet.getRange(colorCellLocation);

  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;

}

3. If you want to name the project, you can change the name, otherwise select Save project

4. In your spreadsheet, enter the function that we just created (COUNTCOLORED). The first argument will be the range that we want to count the colored cells for, and the second argument will be the color we are counting

The formula used in the above example is:

=COUNTCOLORED(A1:A13,A2)

This will count the number of cells in the range A1:A13 that have the same color as A2.

This function that we have just created can count cells highlighted with conditional formatting, or any other colored cells where the fill color has been changed.

Count Colored Cells with an Add-on

The other option for counting colored cells if you don’t want to create a custom script is to download an Add-on that does it.

The Power Tools add-on has a Function by color tool that you can use to count colored cells.

This tool makes it incredibly easy to count the number of colored cells in your spreadsheet.

I am not going to cover how to use this tool in detail in this article. Learn more about counting colored cells with the Power Tools Add-on.

Closing Thoughts

Since there is no built-in functionality for counting colored cells, we have to think outside the box to get this done. We can either create our own function to handle this or just download an add-on that has this feature built into it.

Both are good options depending on which way you want to tackle this problem.

More Google Sheets Tutorials:
How to Use Conditional Formatting Based On Another Cell
How to Count Non-Blank Cells