How to count colored cells in Google Sheets

In this tutorial, we will share 3 methods to count colored cells in a google sheet.

3 methods to count colored cells in Google Sheets

• Using a custom function
• Using the Power Tools add-on
• Using the Function by Color add-on

---

Method 1: Using a custom function

Step 1: Click on Script editor. 

Click on Tools from the main menu bar which will open a dropdown. Select Script editor. 

Step 2: Copy-paste the code and save it.

Clicking Script editor opens the Apps Script in a new window. 

Delete whatever is already present and copy-paste the code below. The code essentially creates a function to count the colored cells. The function will be executed whenever it is called in your Google Sheet. 

function countColoredCells(countRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var countCells = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        countCells = countCells + 1;
  return countCells;
};

(Source)

After copy-pasting the code into your editor, click on the save icon. 

If it has been successfully saved, your save icon will be disabled now. 

Step 3: Call the function.

It’s time to call the function! You can call the function like how you normally enter formulas in Google Sheets.

The function takes in two parameters. One is the range of cells and the other is the cell reference to a color that it has to count. Call the function in the cell you want to display the count. 

Pick a cell to display the count and type in the formula. The formula is: =countColoredCells(A1:A10, C1).

The first parameter represents the range of cells. A before the colon is the start column, 1 is the start row. A after the colon is the end column and 10 is the end row. C1 is the cell reference to the color you want to count. Change your parameters accordingly. 

Step 4: Press Enter. 

After typing in the formula, press Enter to see the count. 

---

Method 2: Using the Power Tools add-on

This method uses an add-on from Ablebits to count the colored cells. 

Step 1: Click on Get Add-ons.

From the main menu bar, click on Add-ons. This will open a dropdown. Now, select Get add-ons.

Step 2: Select Power Tools. 

Clicking Get add-ons will open a Google Workspace Marketplace dialog box. This contains all the available add-ons. In the Search apps text box, type in Power Tools. 

Now, select Power Tools. 

Step 3: Install Power Tools.

After you select Power Tools, you can see the green Install button. Click on it to install the add-on. You will be prompted to allow access to the add-on. You can see a dialog box after successful installation. 

Step 4: Go to Power Tools and click Start. 

Go to Add-ons in the main menu bar. Click on Power Tools and select Start. 

Wait for some time until it opens up the Power Tools dialog box at the extreme right. 

Step 5: Click Function by color and fill in the required details. 

Click Function by color.

Now, fill in the range of cells to be counted. 

Select the cell reference of the color it needs to count. Click on the Color picker, enter the cell reference with the color, and finally click on the blue OK button.

Now change the Use function to COUNTA. Ensure you’ve checked the Paste results to the textbox. The count will be displayed in that cell. 

Step 6: Click Insert function. 

Now, pick a cell you want to display the count and select Insert function. 

Voila, you have the count of red-colored cells now. 

---

Method 3: Using the Function by Color add-on

This is another method to count the colored cells using an add-on. This is a subset of previous add-ons from Ablebits as well. 

Step 1: Select Function by Color from Get Add-ons. 

This is similar to the second method. Instead of installing Power Tools, you install Function by Color. 

Select Function by Color from the dialog box. 

Now, select the blue Install button. Wait for it to be installed. Allow access to the add-on if you are prompted to do so. You can see the below message if you’ve installed the add-on successfully. 

Step 2: Select Start from Function by Color. 

From the main menu bar, click on Add-ons. Then click on Function by Color and select Start. 

This will open a dialog box at the extreme right. Fill in the range and color. Change the Use function to COUNTA. Pick a cell to paste your results to. 

Step 3: Click Insert function. 

Click the blue Insert function button to see the add-on work its magic. 

---

Wrapping up

No more manually counting all your colored cells! You now have three easy-to-use methods at your disposal any time you need to count your colored cells in Google Sheets.