How to Count Cells based on Color in Google Sheets ( Easy Ultimate Guide 2021)

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.

Count Cells based on the Cell Color in Google Sheets

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:

How to rotate text in Google Sheets