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

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 inbuilt into the program, like a way to count colored cells in Google Sheets.

By this, I mean a way 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.

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 colored cells in Google Sheets based on the background color. You can make a copy of our EXAMPE SPREADSHEET and follow along.

So let’s get started!

Familiarize Yourself with Complex Google Sheets Skills

This tutorial covers building your own function using Google Apps Script, which is a pretty advanced skill. If it’s currently a little beyond your capabilities to follow along with this guide, we recommend taking a Google Sheets course on Udemy to brush up on your overall spreadsheet skills first.

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.

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 Editor
   

   

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 code
   

   

4. Click on the Save icon in the toolbar
   

   

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)

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), 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).

How to Count Colored Cells with the SUBTOTAL Function

There’s currently no Google Sheets COUNTIF color formula that you can use to count cells by color. Instead, you can use the Google Sheets SUBTOTAL function to count cells with color. This process is similar to how you would count highlighted cells in Excel.

You will need to use the filter function on your data first to sort by color. We will help you break it down with a step by step guide. Here’s how to count color cells in Google Sheets using the SUBTOTAL function:

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

3. If you go to the filter optin on the header row and select Filter by color > Fill color, You should see your color options.

4. Choose the color you want to count. In our case, we will choose yellow.
5. Now we can use our SUBTOTAL formula =SUBTOTAL(103, A2:A11). The results will be 4.

The 103 is used to specify that the type of subtotal we’re using is the counta version. We use subtotal instead of COUNTIF since it only counts cells that are visible. Countif, on the other hand, is used to count cells with specific texts.

How to Count Colored Cells Using Add-on

You can also count cells by color using the Function by Color Add-On. This add-on is actually part of the Power tools add-on, which has more functionality. You can choose to download the power tools add-on or just the Function by color add-on on its own.

This method is much less intimidating than using Google App scripts.

Here’s how to count cells by color in Google Sheets using the Function by Color Add-On:

1. To download the add-on, Go to Extensions > Add-ons.
2. Click Get add-ons.

3. In the Add-ons window, type function by color and click search.

4. Choose the Function by color add-on and Click Install.

5. You may need to allow permissions. Click Continue and choose your Google account.

To ise the add-on to for Google Sheets count by color:

1. Select the data range.
2. Go to Extensions
3. Choose Function by color and click Start.
4. In the add on window you can choose the background color you want to count and add the formula. In this case we will use the COUNT formula.

5. Click the drop down for formulas and choose COUNTA (text)
6. Click in the paste results to box and choose cell D2

7. You can choose to uncheck the option “Fill results with your pattern colors”

8. Click Insert function.

You will get the results as 4 in cell D2 with a special formula in the formula box.

If you chose to download the power tools add-on instead, then you can find the function by color option on the tool bar for Power tools below.

The add-on is the same so you can follow the steps above to count cells by color.

How to Sum Values by Color

So far we have shown you how to count highlighted cells in Google Sheets. The Function by color add-on is also convenient for other functions other than counting. You can use it to find the sum value by color as well.

Let’s use our example below to find the sum of the marks in yellow:

1. Select the column with the marks.
2. Go to Extensions.
3. Choose Function by color and choose Start.
4. In the add on sidebar, choose the background color.
5. In the drop down menu for use function, choose SUM.

6. Choose where the result will be pasted. In our case, we chose cell B12.

7. Click Insert Function.

You will get the results in cell B12 which is the sum of all the values in yellow. One important to thing to note is that the Functon by color add-on does not update automatically. If you update your spreadsheet, you can always go to Extensions > Function by color and click Refresh results.

Wrapping Up How to Count Colored Cells in Google Sheets

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 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.

Other Google Sheets tutorials you may also like:

• Count Cells IF NOT Blank (Non-Empty cells) in Google Sheets
• How To Count Cells with Specific Text In Google Sheets
• How To Sort by Color in Google Sheets
• How To Color Alternate Rows In Google Sheets
• How To Filter By Color in Google Sheets (Using a Formula)
• How To Highlight Highest or Lowest Value in Google Sheets
• How To Count Checkbox in Google Sheets (Easy formula)
• Can Excel Count Cells with Text? [Easy Guide]
• How To Count Unique Values in Excel [Guide]