Count Cells based on the Cell Color in Google Sheets

The more I use Google Sheets, the more I realize how powerful it is.

And at the same time, there are some things that I wish were an inbuilt part of Google sheets.

One such functionality that I wish Google Sheets had was to count the number of cells based on the background color.

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

Dataset with count of colored cells

But since this functionality is not already a part of Google sheets, you can get this done by using a custom Google apps script to create your own function in Google Sheets.

In this tutorial, I’ll show you how you can easily create a function that will allow you to count cells based on the background color.

So let’s get started!

Count Cells Based on the Background Color

Suppose you have a data set as shown below, and you want to quickly find out the total number of cells that have the green and yellow background color.

Dataset where formula to be used to get count of colored cells

The first step would be to have the Google Apps Script to create a custom function that can do this in Google Sheets.

I tried looking for such a script online and finally found one on this blog.

Below is the Google apps script for the custom function that would allow you to count cells based on the background cell color

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;
};

Here are the steps you need to take to put this script in your Google sheets document:

  1. In your Google Sheets document, click on Tools
  2. In the options that show up, click on Script Editor. This will open the Apps Script EditorClick on Script Editor option
  3. By default, you will be in the Code.gs code window. Delete anything that’s already in there, and copy and paste the above codeCopy paste code in the code window
  4. Click on the Save icon in the toolbarClick on the save icon
  5. Close the Apps Script window.

By doing the above steps, I have added the script code to Google Sheets so that I can now use my newly created custom formulas within the worksheet (just like regular formulas).

Note: In case you already have some other code in the Code.gs window and you don’t want to mess it up, it’s best to insert a new Script file and paste the code there. To do this, click on the Plus icon next to Files and then select Script. This will insert a new script file (give it a name) and copy-paste the above code in this new script file.

Now that we have the code in place, let’s see how to use the custom formula to count cells with a specific background color.

In our example, I need to count all the names that are in green color.

To do this, enter the below formula in the cell where you want the count of the colored cells:

=countcoloredcells(A2:A11,C1)

Formula to count colored cells in Google Sheets

In the above formula, I have used cell C1 as the one from where the formula should pick up the background color. You can also use any cell within the data range to get the color and then count based on it.

Just like the formula to count green-colored cells, the below formula counts all the yellow-colored cells:

=countcoloredcells(A2:A11,D1)

How does this formula work?

Let me quickly explain what happens here.

This custom formula that we have created takes 2 arguments:

  • the range of cells that have the colored cells that we need to count
  • the cell that has the background color that needs to be counted

In our example, I have taken the second argument as cell C1, as it also has the same color that I want to count. If you want, you can pick any cell from the range and use that instead of creating a new one.

For example, you can choose cell A3 instead of C1 as the second argument, as it also has the same color.

When you use this formula, it goes through all the cells in the range and checks the background color of all the cells. If the background color of a cell matches the one from the second argument, then that it’s, counted else it’s not.

Once the formula has gone through all the cells in the range, it simply returns the total number of cells it found that had the same background color.

Note: You can use the same formula to count cells with different background colors. For example, if in the same data set I have green and red then I can use this formula in two different cells – one that refers to the green cell and counts the total number of green cells, and one that refers to the red cell count the total number of red cells.

How to Make the Formula Automatically Update On Change

While the formula works perfectly and counts the number of cells with a specific background color, there is one minor irritant.

It does not automatically refresh when you make a change. For example, if I remove the color from one of the cells or I manually add color to one of the cells, then the formula would not automatically update to give me the right result.

Even if I go to the cell that has the formula, go into the edit mode, and hit enter, it would still not recalculate.

The reason for this is because it does not consider this as a change that entails recalculation. I even tried refreshing the workbook and it also did not work (maybe because of some cache issue)

Unfortunately, there is no automatic way to do this, but there is a really simple manual method to do this.

So here is the fix – go to any of the cells in the range, get into the edit mode (press F2 or double click on the cell), and add a space character at the end of the cell content, and hit enter. As soon as you do this, you’ll see that the formula recalculates and gives you the right result.

Now you can go back to the cell where you have made this change by adding a space character and remove it.

While this is inconvenient, as of now this is the only way I could figure out to make sure that we get the correct count of the number of colored cells using this custom formula.

If you’re working with a large data set, this formula may take a few seconds or even a few minutes to calculate the total number of cells with a specific background color. You may see a ‘Loading’ text in the cell during that time (as shown below).

Showing loading when formula result is being updates

So this is how you can create and use your own custom function to count sales based on the background color in Google Sheets.

I hope you found this tutorial useful.

Other Google Sheets tutorials you may also like:

Sumit

Sumit

Google Sheets and Microsoft Excel Expert.