Colored Cells are a great way to organize data, they help structure the visual appearance of data ensuring it is easy to read. There are where counting the colored cells in a data set is useful.
There are multiple methods to count colored cells in Google Sheets. We can use a custom function to achieve this or by using add-ons, in this article we will show you the various methods.
For this demonstration we have created an example data set, this shows a list of tasks with varied colored backgrounds representing importance:
We want to find the total number of red, yellow and green cells to understand the amount of tasks by priority.
After selecting Apps Script a window will display in a new tab containing the following default code:
function myFunction() {
}
Select and delete this code so that the window shows the following:
Copy the below function and paste into the blank function window:
function countColoredCells(countRange,colorRef) {
var activeRange = SpreadsheetApp.getActiveRange();
var activeSheet = SpreadsheetApp.getActiveSheet();
var activeformula = activeRange.getFormula();
var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
var backGrounds = activeSheet.getRange(countRangeAddress).getBackgrounds();
var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
var BackGround = activeSheet.getRange(colorRefAddress).getBackground();
var countColorCells = 0;
for (var i = 0; i < backGrounds.length; i++)
for (var k = 0; k < backGrounds[i].length; k++)
if ( backGrounds[i][k] == BackGround )
countColorCells = countColorCells + 1;
return countColorCells;
};
Press the “Save project” icon which looks like this:
The icon will become greyed out once completed successfully, you can close this tab and return to your sheet.
Select the destination to display the total value of colored cells and use the custom function just saved. This can be achieved by inserting the formula:
=countColoredCells(RANGE, DESTINATION COLOR)
Formula breakdown:
=countColoredCells
This calls our custom formula copied into the Apps Script window and saved
RANGE
This is the range of the data to be included in the function
DESTINATION COLOR
This is a cell that contains the background color we wish to count
See this in action:
Press enter and you will see the cells specific to the chosen color are totaled and displayed.
When selecting “Get add-ons” a popup window will display the Workspace Marketplace.
In the search bar type “Function by Color” and press enter, look for the Function by Color add-on provided by Ablebits which looks like:
Click this add-on and select INSTALL on the next screen. At this point you may need to provide permission, select continue, choose your Google account and select accept. You will be presented with a success screen showing that Function by Color is installed.
Select DONE and close the popup window.
To use the Function by Color add-on select extensions from the toolbar. You can now see Function by Color, select this and from the submenu select Start.
The Function by Color tool menu will display. Click the “Select range” box and enter the cell range to be included. Click the color picker tool and you will be presented with a Function by Color pop up window:
Select the cell containing the background color to be totaled and select OK. Next, select the Use Function drop down menu and choose the option COUNTA (TEXT).
In the Paste results to: box enter the cell you want the results to be displayed and press Insert function. The add on will count the cells in the range with your chosen color and populate the destination cell with the results.
When selecting “Get add-ons” a popup window will display the Workspace Marketplace.
In the search box type “power tools.”
Select the Power Tools from Ablebits and click INSTALL, provide permission by selecting continue, choose your Google account and select accept. You will be presented with a success screen informing you that Power Tools has been installed. Close the Pop-up window and return to your sheet.
Select extensions from the toolbar. You can now see Power Tools, select this and from the submenu select Start. The power tools dialogue will then display on the right hand side of the window, look for the icon Σ and click it, a drop down menu will appear. Select Function by color as seen in the image below:
The Power Tools add-on will initiate the Function by color feature.
The function by Color tool menu will appear:
Function by Color tool menu breakdown:
Select Range:
The cell range containing the colored cells you wish to know the total.
The color picker icon:
Clicking this will display a pop up window, click a cell containing the same background color you need to be counted and select OK.
Use Function:
Selecting this will display a drop down menu, select COUNTA (TEXT).
Paste results to:
Input the cell you want the results to appear in.
Insert function:
Selecting this will request the add on to total the chosen colored cells in the range specified and your destination cell will be populated with the results.
If you enjoyed this article, you might also like our article on how to get the word count in Google Sheets or our article on how to count characters in Google Sheets.
If you want to learn how to send email notifications from Google Sheets, we also suggest checking out our detailed guide.
