How to Count Colored Cells in Google Sheets (Step-by-Step)

The topic for this tutorial is how to count colored cells in google sheets. A lot of times, we color code our data in Google Sheets and once the volume of our data increases by a lot, we may need to remove duplicates or have a summarization of the number of cells which are colored in different colors.

Although Google Sheets contain a plethora of functions for different purposes, there are still a few tasks for which there no functions built into Google Sheets. So, since Google Spreadsheets does not have its own function to count cells with background colors; I can show you how you can still create a customized function to help you with this task.

You will do this by using the Google Apps Script.

You may love to read our one of very related tutorial on Google Sheets count cells with specific text.

Let me get right into it then!

Creating a Custom Function to Count Cells Based on Background Color

First of all, I will give you an example of a set of data; which I have color coded in multiple colors.

Creating a Custom Function to Count Cells Based on Background Color

As you can see in my example, I have set of data which has cells with more than once background color. Now, what if I need to know the number of cells with each of the background colors shown?

To begin with, I will need to build a custom function to perform this task using Google apps script. Follow the steps below to know how you can achieve this easily.

1. Find the second last option “Extension” from the top menu bar on your Google spreadsheet.

Google Apps Script Option in Google Sheets Menu

2. Afterwards, you need to find the “Apps Script” from the drop-down menu. Doing this will automatically open the Apps Script Editor.

open the Google Sheets Apps Script Editor

3. A new window for “Code.gs” will open by default and there will already be code there. You need to remove any code that is already given and give the code laid out just 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;
};
function countColoredCells code add in Code gs of Google App Script

4. After you have given the input for your code for the relevant function, click on the “Save” option.

Save the function

5. If you have saved your code, you can close the Code.gs window.

Now, I can use the formula I have just created for, counting cells with background colors, just like any other regular formulas in Google Spreadsheet.

How to Count Colored Cells (Google Sheet Count Colored Cells)

In my dataset, I have the colors red, green and yellow. Suppose that I want to know the number of cells which have the background color red.

  1. Select a cell and type the formula:
=countcoloredcells(A2:A17,C1)
How to Count Colored Cells in Google Sheets

The Syntax of the Formula for Counting Cells by Cell Color

Notice that there are two augments to this formula:

  1. The first augment is the range is cells
  2. And, the second augment is the cell address for the color reference.

The first augment sets a specific range within which, Google spreadsheets will carry out its search. In this instance, my values are from cell A2 to cell A17. Moreover, the second augment is a cell address which has the color I am counting. In my example, I have chosen cell C1 for this criteria; however, I could have given A2, A4 or A6 as well.

How to Count Colored Cells (Google Sheet Count Colored Cells)

In summary, my spreadsheet will search the cell range given in the first augment to identify and match the color in the cell address given as the second augment.

Updating Changes in the Data Automatically

As useful as this formula is, there is a small setback to it.

Suppose for some reason, you need to modify your data; by removing a colored cell from the data or by adding a new, different color or, by, copying and pasting a colored cell. Any of these changes will require a recalculation of your formula to count the colored cells, right?

Sadly, Google Sheets does not apply these changes to its calculation on its own.

But do not worry, since there is simple and short manual way to get around this issue.

All you have to do is, after you make a change to any of the cells in your data, you have to simply convert that cell into edit mode (either by double-clicking on it, or, pressing F2 after selecting the cell) and then add one space right at the end of your data. (Press the spacebar key on your keyboard at the end of the data in that cell). This will prompt Google Sheets to perform a recalculation by considering the change.

And, that is how you can count cells with the same color in Google Spreadsheets by creating your own function.

I hope this tutorial helps you to count colored cells in google sheets.

Other Google Sheets Tutorials of Us, You May Love to Read:

Conditional Formatting Based on Another Cell in Google Sheets

Keyboard Shortcuts for Google Sheets

How to Search IF Cell Contains in Google Sheets

Google Sheets Formulas Not Working (Refresh Formulas)

How to Use the Find and Replace Function in Google Sheets

Was this article helpful?
YesNo


Related More Articles

3 thoughts on “How to Count Colored Cells in Google Sheets (Step-by-Step)”

  1. for(var i=0;i<bg.length;i++)
    for(var j=0;j 0)
    count=count+1;
    return count;
    // Count only colored cell with positive content.
    ———–
    “var values = range.getValues();”
    This line is only necessary in this kind of case.

    Reply
  2. Hi, I’ve been using this to count my colored cells on Google Sheets for a while now. However it hasn’t been working since this morning, and the cells are stuck with the “Loading…” error since forever. Would you be able to help?

    Reply

Leave a Comment