# 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:

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

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:

### Step 3: Insert our custom function

Copy the below function and paste into the blank function window:

function countColoredCells(countRange,colorRef) {

var activeformula = activeRange.getFormula();

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:

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:

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

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:

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.

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

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:

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

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.

## Method 3: Use the Power Tools Add-on

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

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:

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

The function by Color tool menu will appear:

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:

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