How to Count Colored Cells in Google Sheets

Have you ever wanted to know how to calculate all red or green cells in a Google sheet? This short and easy tutorial shows you how to count colored cells in a Google Sheet.

Before jumping into the methods, we will first discuss why cells are colored in Google Sheets and their purpose.

Interesting? Let’s Start! 🙂

Use of Colored Cells in Google Sheets

The more we use Google Sheets, the more we appreciate its capability. Colored cells help arrange the visual look of data, making it easier to read. This is indeed a helpful tool for data organization.

For example, we have a data set with a list of some tasks, and the cells need to be arranged based on their priority (High, Medium, Low). We can highlight the priority with three colors for such a data set.

This will help us better organize and understand the data. There are many ways by which you can highlight cells or rows. Our article on how to highlight every other row in Google Sheets can assist you.

How to Count Colored Cells in Google Sheets

Although Google Sheets does not provide any built-in method for counting colored cells, there are many ways to do so.

We will discuss some of the most efficient techniques in this article.

Open the Example Sheet

Follow Along With Our Example Data for Counting Colored Cells

If you don’t have data to work with, click the above button to open our sample data in Google sheets. Once it is open, go to File > Make a copy to create a duplicate for your own use.

Method -1: Use of Custom Function

This method will create a custom function in the App Script. This function will use argument of the range of cells from which we need to count colored cells and return the count. For this, follow the following steps:

Step 1: Create the dataset

The first step in this method is to create the dataset for which you need to count the colored cells. The dataset shows the test marks in 3 tests. In this example, we will use the dataset for the test grades of 15 students.

All the cells that are highlighted with green color are “A Grade” students, cells highlighted with orange color are “B Grade” students, and finally, the cells highlighted with red color are “F Grade” students.

Suppose we need to count the number of all grade students. The dataset is shown in the image below:

How to Count Colored Cells in Google Sheets

Step 2: Write the Custom Function

After creating the dataset, click “Extensions” and select “App Script”. Clicking on “App script opens a new window. In the left pane, select the “Code.gs” file. This file contains some default code, as shown in the image below:

Remove the code from the file and paste the following code into this file:

function CountColoredCells(range,color) {
 
  //Fetch the formula from current cell
  var curr_range = SpreadsheetApp.getActiveRange();
  var curr_sheet = curr_range.getSheet();
  var curr_cellformula = curr_range.getFormula();

  //Parse and convert first argument to range reference
  var param1 = curr_cellformula.match(/\((.*)\,/).pop();
  var countRange = curr_sheet.getRange(param1);
  var countBgColor = countRange.getBackgrounds();

  //Parse and convert second argument to range reference
  var param2 = curr_cellformula.match(/\,(.*)\)/).pop();
  var target_cell = curr_sheet.getRange(param2);
  var targetCellBG = target_cell.getBackground();
  
  var counter = 0;
  //count the cells in which color of both cells
  for (var i=0;i < countBgColor.length; i++) {
    for (var j=0;j < countBgColor[0].length; j++) {
      if (countBgColor[i][j] === targetCellBG) {
        counter++;
      }
    }
  }
  return counter;
};

This function takes as an argument two parameters; one is the range of the cells for which we need to count colored cells, and the second is the color which we need to count. For the 2nd argument, we can pass any cell with the same background color. After writing the complete function, remember to click the save button on the top menu bar.

When the function is saved, close the window and return to your sheet.

How to Count Colored Cells in Google Sheets using App Script

Step 3: Use the Custom Function

To use this function, point to the cell where you need to get the resultant count and call the function like this:

=CountColoredCells(B2:D15,F1)

Here B2:D15 is the range of cells containing the marks of students. Since we need to get the total no of A grades, highlighted with green color, cell F1 also has the same green color, so we passed F1 as the second argument.

Make sure you can pass any of the Green cells as the second argument because this is just to tell the function of the color for which cells need to be counted.

Repeat the same process with the other two colors as well, and you will get the following results:

Congratulations 🙂 You have successfully run the script to count the number of colored cells in Google sheets.

Method 2: Use the Power Tool Add-on

The second method to count the colored cells is to use an Add-on “Power Tool.” This can be installed in Google Sheets using Google Workspace Marketplace. The following steps should be followed:

Step 1: Install the Power Tool Add-on

On your Google Sheets, click the “Extension” menu on Google Sheets, and select Add-ons → Get Add-ons.

Clicking this menu will open up a Google Workplace Marketplace pop-up window. In the search bar of the window, type “Power Tools.” This will search for the required Add-on in the marketplace.

Select the “Power Tools” add-on and “Install” from the next window.

Choose the Ablebits Power Tools option, click INSTALL, grant permission by choosing Continue, pick your Google account, and finally, select Accept. A success screen letting you know Power Tools have been installed will appear. Return to your sheet and close the pop-up window.

Step 2: Use the Power Tools Add-on

After successfully installing the Power Tools add-on, you can use it to count colored cells in your dataset. Select “Extensions” and select “Power Tools” -> Start.

This will show the Power Tools options window on the right side of the screen. From that screen, click the ∑ icon and select the “Function by Color” option from the drop-down menu.

Clicking it will open the Function By Color menu. This menu contains the following options:

Range: This is to specify the range of cells for which you need to count the colored cells.
Select Color: This option selects the color you need to count.
Use Function: This option lists down different functions you can use. For counting colored cells, we will select the “COUNTA(text)” function.
Paste Result to: This specifies the cell where you need to place the resultant count.
Calculate in: This option selects whether you need to count the specified color cells in the complete selection or get the count column-wise or row-wise. For our example, we will choose the “Entire Range” option.
Finally, uncheck the “Fill Results with your pattern color” option.

This will give the green color count in the entire dataset in the F2 Column. You can also repeat the same process for other colors and get the results. The final output will look like this:

Method 3: Use the “Function by Color” Add-on to count colored cells in Google sheets

This add-on works the same as we have done with the Power Tools add-on. The following steps will be followed to count the cells using the “Function by Color” add-on.

Step 1: Install the “Function by Color” Add-on

First, we must get the add-on by selecting the Extensions ->Add-ons -> Get Add-ons menu.

Clicking this menu will open up a Google Workplace Marketplace pop-up window. In the window’s search bar, type “Function by color.” This will search for the required Add-on in the marketplace.

Choose the Ablebits “Function by Color” option, click INSTALL, grant permission by choosing Continue, pick your Google account, and finally, select Accept. A success screen letting you know Power Tools have been installed will appear. Return to your sheet and close the pop-up window.

Step 2: Launch the “Function by Color” Add-on

After successfully installing the “Function by color” add-on, you can open it to count colored cells in your dataset. Select “Extensions” and select “Function by Color” -> Start.

Clicking it will open the Function By Color menu. This menu contains the following options:

Range: This is to specify the range of cells for which you need to count the colored cells.
Select Color: This option selects the color you need to count.
Use Function: This option lists down different functions you can use. For counting colored cells, we will select the “COUNTA(text)” function.
Paste Result to: This specifies the cell where you need to place the resultant count.
Calculate in: This option selects whether you need to count the specified color cells in the complete selection or get the count column-wise or row-wise. For our example, we will choose the “Entire Range” option.
Finally, uncheck the “Fill Results with your pattern color” option.

Repeat the same process for other colors as well. In the end, you will get the following output:

Conclusion

We discussed the three most effective and commonly used methods to count the number of colored cells in your Google Sheet.

The methods discussed in this article are very efficient and straightforward in use. You can opt for any method according to your dataset and requirements.

We hope you like the tutorial and find it the best fit for your problem scenario.