colored cells are cells containing filled in colors to highlight or give reference to the data input inside. Sometimes, when there are multiple colored cells on the worksheet, we might want to count them since they highlight important data in the document.
But unfortunately, the google sheets app doesn’t have an inbuilt way or tool used to count the colored cells in the worksheet but there are a few ways you can go about it.
In this tutorial, we are going to discuss the three major methods used in counting colored cells in Google sheets.
Method 1: How To Count Colored Cells In Google Sheets Using the Custom Function.
Unlike most tasks and calculations done on Google sheets that require a function to produce results, there is no function used to count colored cells yet, so we are going to create one of our own using the Apps Script.
In the sample worksheet below, there are grades of several students in different subjects, represented in color. Red means below average or Fail while Green means above average or Pass.
Step 1: copy the below code.
/////////////////////////////////////////////////
/**
* @param {range} countRange Range to be evaluated
* @param {range} colorRef Cell with background color to be searched for in countRange
* @return {number}
* @customfunction
*/
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;
};
/////////////////////////////////////////////////
Step 2: Come back to your Google sheet. Select Extensions on the toolbar.
![3 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/3-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 3: Click on the Apps Script option here.
![4 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/4-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 4: You’ll be redirected to a new window titled App Script as seen below.
![5 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/5-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 5: Click on Code g.s
![6 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/6-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 6: A random function is pasted on the sheet, delete the code and paste the new code copied earlier.
![7 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/7-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 7: You can rename the script, it’s not compulsory but I renamed mine as COUNT COLORED CELLS.
![8 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/8-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 8: Click on the Save icon and go back to your worksheet.
![9 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/9-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
What we have done in the previous steps is that we created a new function that can be used to count colored cells which is:
=CountcoloredCells(cell range, colored cell)
Where;
● Cell range – the entire range containing the colored cells you want to count.
● colored cell – the cell containing that particular color type you want to count such as green, yellow, blue etcetera.
Step 9: Input the formula in the fx tab or whatever cell you want your results to appear.
Step 10: Select the cell range of the worksheet and the cell containing the color you want to count.
![10 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/10-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 11: Click Enter. A Loading text appears in the cell. It might take some seconds to a minute depending on the size or number of colored cells in the worksheet.
![11 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/11-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
The total number of cells with the Red color is counted.
![12 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/12-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Repeat the steps to count the cells that are colored green.
![13 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/13-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
This method has a few disadvantages. If a new cell is added containing the already counted color, the result won’t be updated unless you re-select the cell range and other parameters in the formula or you add a space in front of the text input in the colored cell, then the results would be updated.
Method 2: How To Count Colored Cells In Google Sheets Using the Function By color Add-on.
Add-ons are added features bought from the Google Workspace Marketplace to improve the efficiency and effectiveness of Google Sheets. An example of an Add-on is the Function by color tool. Some Google sheets users do not have this add-on on their worksheets yet.
But with the steps stated below, you can get this add-on up and running.
Step 1: Click on Extensions on the toolbar.
![14 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/14-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 2: Click on Add-Ons.
![15 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/15-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 3: Click on Get Add-ons.
![16 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/16-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 4: You would be redirected to the Google Workspace Marketplace website. Various apps, innovations and tools are sold here. Some are paid for while some are for free.
![17 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/17-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 5: Search for Function by Color in the search bar.
![18 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/18-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 6: The Function by Tool feature is displayed on the screen.
![19 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/19-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 7: Click on the app. Click on Install.
![20 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/20-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 8: You have to grant permission for the app to access your Google sheet.
![21 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/21-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 9: You would be redirected to confirm your google account to be linked with the app. After the permissions are granted and the account has been verified, the function by color app becomes installed as an add-on in your Google sheet.
![22 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/22-How-To-Count-Colored-Cells-In-Google-Sheets-712x1024.jpg)
Step 10: The Function by Color feature is placed under the Add-ons tab.
![23 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/23-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Now, we want to use the newly installed to count colored cells.
Step 1: Click on Extensions on the toolbar.
![24 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/24-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 2: Select Function by color.
![25 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/25-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 3: Select Start.
![26 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/26-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 4: A tab is displayed by the side of the worksheet.
![27 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/27-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 5: Select the range containing the colored cells.
![28 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/28-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 6: Select the specific color that you want to count. It can be selected by
● Clicking the fill color icon and selecting the color manually.
![29 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/29-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
● Select a sample cell with a color from the worksheet.
![30 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/30-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Click ok.
![31 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/31-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 7: Select the function you want to apply to the cells.
Note: If you want to count cells with text input in the cell, you use the COUNTA(text) function or if you want to cells with numbers, you use the COUNT(numbers)
![32 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/32-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 8: Click on the Paste results to select the row you want the results to appear.
![33 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/33-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 9: Click on Insert Function to enter the results. The colored cells are counted automatically.
![34 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/34-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
The red cells are counted automatically.
![35 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/35-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Repeat the steps to count the green cells in the worksheet.
![36 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/36-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Method 3: How To Count Colored Cells In Google Sheets Using Power Tool Add-On.
Another prominent add-on used to count colored cells is the Power Tool. This add-on contains a collection of excellent tools that decreases workload and saves time.
If you don’t have this feature on your worksheet, you can follow the steps in the previous method on how to successfully install the Power tool add-on from the Google Workspace Marketplace.
Step 1: Click on Extensions on the toolbar.
![37 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/37-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 2: Click on Power Tools.
![38 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/38-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 3: Click on Start.
![39 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/39-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 4: The Power tool tab is opened by the side of the worksheet. Click on the Function by color icon.
![40 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/40-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 5: Select the range, the function you want to use, the color you want to count and where you want the results pasted to.
![41 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/41-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
![42 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/42-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Step 6: Click Insert Function and the red cells are counted. Repeat the procedures to count the green cells and the results are generated below.
![43 How To Count Colored Cells In Google Sheets](https://abidakon.com/wp-content/uploads/2022/04/43-How-To-Count-Colored-Cells-In-Google-Sheets.jpg)
Conclusions:
The listed methods would help you to know How to count colored cells in google sheets accurately and quickly. I hope you found this tutorial beneficial. Thank you.