In this article:

Count Colored Cells in Google Sheets (The Easy Way!)

May 8, 2024

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.

How to Count Colored Cells in Google Sheets

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.

Method 1: Use our Custom Function

Step 1: Open your existing file or create a new data set.

For this demonstration we have created an example data set, this shows a list of tasks with varied colored backgrounds representing importance:

Count color cells using Google Sheets - Example data set

We want to find the total number of red, yellow and green cells to understand the amount of tasks by priority.

Step 2: Click Extensions > Apps Script

Count color cells using Google Sheets - Apps script

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:

Count color cells using Google Sheets - Blank function window

Step 3: Insert our custom function

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:

Count color cells using Google Sheets - Save project icon

The icon will become greyed out once completed successfully, you can close this tab and return to your sheet.

Step 4: Use the custom function

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:

Count color cells using Google Sheets - Implementing the custom formula

Press enter and you will see the cells specific to the chosen color are totaled and displayed.

Method 2: Use the Function by Color Add-On

Step 1: Select Extensions > Add-ons > Get add-ons

Count color cells using Google Sheets - Get Add-ons

When selecting “Get add-ons” a popup window will display the Workspace Marketplace.

Step 2: Install the Function by Color Add-On

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:

Count colored cells using Google Sheets - Install Function by Color add-on

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.

Count color cells using Google Sheets - Install Function by Color success

Select DONE and close the popup window.

Step 3: Utilize the Function by Color Add-On

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

Count color cells using Google Sheets - Function by Color tool menu

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:

Count color cells in Google Sheets - Function by Color target color

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).

Count color cells using Google Sheets - Function by Color use function

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.

Count color cells in Google Sheets - Function by Color Results

Method 3: Use the Power Tools Add-on

Step 1: Select Extensions > Add-ons > Get add-ons

Count color cells using Google Sheets - Get Add-ons

When selecting “Get add-ons” a popup window will display the Workspace Marketplace.

Step 2: Install the Power Tools Add-On

In the search box type “power tools.”

Count color cells in Google Sheets - Power Tools add-on

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.

Step 3: Utilize the Power Tools Add-On

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:

Count color cells using Google Sheets - Power Tools function by color

The Power Tools add-on will initiate the Function by color feature.

The function by Color tool menu will appear:

Count color cells in Google Sheets - Function by Color menu

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:

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. 

Get Google Sheets productivity and automation tips delivered straight to your inbox
Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.
We'll email you 1-3 times a week — and never share your information.

Work less, automate more!

Use Lido to connect your spreadsheets to email, Slack, calendars, and more to automate data transfers and eliminate manual copying and pasting. View all use cases ->