In this tutorial, we will see how to count cells based on color in google sheets.
Google sheets has multiple useful features .However some feature are not availble by deafult like counting cells based on colors.
Let’s find out how we can count cells based on color in google sheets.
How to Count Cells based on Color in Google Sheets : Step by Step Guide
- With the Power Tools add-on
- With Function by Color add-on
- With a custom function
Count cells using the function by color add-on
Step 1: Open google sheets with the list where you need to count cells based on color.
In this tutorial, we will count the cells based on color for the below google sheet.
Go to the Main Menu and click on Add-ons. Under Add-ons go to ” Get add-ons”
Once you click n “Get add-ons” you will be taken to the “Google Workspace Marketplace ” window as shown below.
Search for “Power tools” in the search area as shown below.
Step 2: Install the “Power Tools” addon.
Click on the Power tools app and you will see the below screen.
Click on Install
Click on continue.
Log on to your Google account.
Power tools will need the necessary permission to continue with the installation.
Click on Allow
Click on done
Once you come back to your google sheet. You will see Power tools on the right-hand side bottom of your sheet as shown below.
If you do not see the pop-up with Pop Tools. You can also start the Power Tools from the Main Menu via
Add-ons->Power Tools->Start
Step 3: Select the cells in the google sheet Go to Power tools
In the Power tools menu, select the “Function by color ” option as shown below.
Select the range of cells that need to be counted
Use the option “Auto-Select” to select the range automatically and click OK.
Select the color via the Fill Color picker option.
In this example, we want to count the cells with city names in Green color.
Specify the cell where you want to paste the result and click OK. In this example, it will be D16.
Now click on the Insert function as shown below.
As soon as you select, Insert function you will see a count of cell-based on green color.
With Function by Color add-on
Step 1: Install the Function by color add-on from Google Workspace Marketplace.
Step 2: Once the Function by Color Add-on is installed.
Go to Add-on ->Function by Color->Start in the Main Menu Toolbar
Step 3: Select Range for Cells
Once you start the Function by Color addon. You can see the Function by Color add-on extension on the right bottom of your google sheet as shown below.
Select the range
Here we are selecting the range from A2:A13 ( A2 cell Denmark to A13 Ethiopia)
Click on OK
Step 4: Select Color for Cells
Here you need to Select a pattern cell to use its colors for counting.
Click OK
Step 5: Change the Use function
Change the use function to COUNTA(text)
From the drop-down option for Use function, select COUNT(text)
Step 6: Select the Cells for Result.
Select the cell where you want to display the result. In this example, we will display the result in C16.
Step 7: Execute the Function
Now click on the Insert function.
Once you click on Insert Function by add-on color will execute and display the desired result as shown below.
Using a custom function
Step 1: Go to Add-on-> Script editor on the Main Menu.
Step 2: Once you click on Script editor. It will start the App script editor in a new window.
Copy and paste the below script. (Source)
======================================================================
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;
};
Save the project and go back to google sheets.
Step 3: Enter the function to display the script
Copy and paste the following function “ =countcoloredcells(A2:A13,C1) ” in the cell where you need to display the result
You will get the result as shown below . The count for the number of Cities marked in yellow is 5.
We hope this tutorial on “How to count cells based on color in google sheets” was useful.
Other Useful Articles: