In this article, we would have a look at how to 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 as shown below. Each row has been color-coded based on whether the order has been fulfilled or not. Suppose that the fulfilled orders have been coded red and the unfulfilled ones red. We can separately count the number of cells for both of these colors to find the total of both types. Or suppose that we’ve color-coded certain cells and we want to count the cells with color. This is certainly a useful way to quickly count data.
Counting cells with color in Google Sheets however can be somewhat tricky as Google Sheets does not allow us to do this using an inbuilt feature. So, we’ll be using the Google Apps Script to add a custom formula that we can use to count colored cells in Google Sheets. 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 add a feature that will allow us to count cells by color in Google Sheets.
Google Apps Script is a feature provided in Google Sheets by which we can write codes to implement niche and customizable functions for our own need. This allows us greater flexibility and freedom to do whatever we want by adding custom features that Google Sheets does not have natively.
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. Instructions on how to add this script to Google Sheets is given 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;
};How to add scripts to Google Sheet Project to add custom features
Follow the given steps to add this script to your Google Apps Script.
- Go to Extensions>Apps Script.
- Name the script as you like.
- In code.gs, remove any code present and paste the code written above.
- Your screen should look like this.
- Click save icon to save the project 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 learn 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.
Since we only want to count the number of entries and not all cells that are color-coded, so we’ll take only one of the columns as the range. If we select all the whole array of data, then we’ll get repeated entries.
To count the number of red cells our formula would be as under.
=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 to count the cells with green color.
=countcoloredcells(A2:A28,I4)Make sure that the referenced color and the cell color are exactly the same. Otherwise the formula will return 0.
If you want to count the number of cells of a particular color in an array of cells, the select the whole array as the range.
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.
How to scrape Google Search results from Google Sheets: Scrape Google Search results for a specific keywords and automatically update the data.