Count cells by color in Google Sheets

Count cells by color in Google Sheets
Reading Time: 4 minutes

In this article, we would have a look at how to count cells by color in Google Sheets. 

Counting cells by color in Google Sheets
Count cells by color in Google Sheets

Count cells by color in Google Sheets: Sample Case

Let’s say we have a table of Order Details. Each row has been color-coded based on the fact if they have been fulfilled or not. Our target is to count the number of orders that have been color-coded red and the same for the green color. 

Sample dataset
Figure 1: Sample Dataset

Doing this can be a bit tricky as Google Sheets does not allow us to do this using an inbuilt feature. However, this can be done using the Google Apps Script. We will discuss how the approach works in the next section of this article.

How to count cells by color in Google Sheets

As previously mentioned, we will be making use of the Google Apps Script to achieve the given problem statement.

Google Apps Script is a feature provided in Google Sheets by which you can write codes to implement niche and customizable functions for your own need. This allows us greater flexibility and freedom to do whatever we want. 

To count cells by color in Google Sheets, we will have to write a custom function. The custom function should be able to count the number of cells based on the cell background color. This blog has already done the research and written the script for the same. We will be using this as the script and do the further required steps.

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;

};

How to add the custom script to your Google Sheet Project

Follow the given steps to add this script to your Google Apps Script.

  • Go to Extensions>Apps Script
  • Name the Script However you like
  • In code.gs, remove any code present and paste the code written above in the article.
  • Your screen should look like this 
Preview of apps script editor
Figure 2: Apps Script Editor Preview
  • Click on Save and close the tab. 

You have successfully added a script to your Google Sheets file. If you have any other script in Code.gs that you don’t want to disturb, you can make a new script in Apps Script Editor. In the next section, we will talk about how to use the custom function to count cells by color in Google Sheets. 

How to use the custom formula to count cells by color in Google Sheets

The custom formula that we just added has the following syntax.

=countcoloredcells(range, sample_cell)

Range: This is the range in which you want to count the cells 

Sample_cell: This is a sample cell with the same color you want to count

Now we will use the same function to calculate the number of green and red rows in our sample sheet. 

Our range would only be limited to the first column since we want to count the number of entries. If we had included the entire table then we would have gotten repeated entries.

To count the number of red cells our formulae would be. 

=countcoloredcells(A2:A28,I3)

We have used cell I3 as a sample cell as a reference to the color we want to count. Similarly, you can write a function for Green color which would be.  

=countcoloredcells(A2:A28,I4)

Cells in Google Sheets by color
Figure 3: Count cells by color in Google Sheets

Conclusion

We saw how to count cells by color in Google sheets using the app Script Editor. However this method is not dynamic, i.e. it doesn’t change when you make some changes in your code. One way to tackle this is to update it manually. To update the count manually, just go to the cell and add a space in the end and hit enter. It should then recalculate the value.

See Also

Want to know more formulas and functions in Google Sheets? Look at our definitive guide on Google Sheets which covers hundreds of such topics here. Enjoy reading!

Filter Data by Color in Google Sheets: In this article, we would have a look at how to Filter Data by Color in Google Sheets.

How to group rows in Google Sheets: We will learn how to group rows in Google Sheets. We will also have a look at nested grouping.