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.
Step 3: Click on the Apps Script option here.
Step 4: You’ll be redirected to a new window titled App Script as seen below.
Step 5: Click on Code g.s
Step 6: A random function is pasted on the sheet, delete the code and paste the new code copied earlier.
Step 7: You can rename the script, it’s not compulsory but I renamed mine as COUNT COLORED CELLS.
Step 8: Click on the Save icon and go back to your worksheet.
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.
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.
The total number of cells with the Red color is counted.
Repeat the steps to count the cells that are colored green.
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.
Step 2: Click on Add-Ons.
Step 3: Click on Get Add-ons.
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.
Step 5: Search for Function by Color in the search bar.
Step 6: The Function by Tool feature is displayed on the screen.
Step 7: Click on the app. Click on Install.
Step 8: You have to grant permission for the app to access your Google sheet.
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.
Step 10: The Function by Color feature is placed under the Add-ons tab.
Now, we want to use the newly installed to count colored cells.
Step 1: Click on Extensions on the toolbar.
Step 2: Select Function by color.
Step 3: Select Start.
Step 4: A tab is displayed by the side of the worksheet.
Step 5: Select the range containing the colored cells.
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.
● Select a sample cell with a color from the worksheet.
Click ok.
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)
Step 8: Click on the Paste results to select the row you want the results to appear.
Step 9: Click on Insert Function to enter the results. The colored cells are counted automatically.
The red cells are counted automatically.
Repeat the steps to count the green cells in the worksheet.
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.
Step 2: Click on Power Tools.
Step 3: Click on Start.
Step 4: The Power tool tab is opened by the side of the worksheet. Click on the Function by color icon.
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.
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.
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.
