This post is going to show you the three different ways to count colored cells in Google Sheets.

You might have color-coded your data in Google Sheets to make it easier to read. Unfortunately, this can make analyzing the data more difficult.

Understanding how to count colored cells in Google Sheets may be helpful to you.

In this post, you will learn the different methods you can use to count cells based on the background color in Google Sheets.

• Count colored cells with the SUBTOTAL function.
• Count colored cells with an App Script Custom Function.
• Count colored cells with an Add On.

Download a copy of the sample workbook using the above link to follow along with the post.

Count Colored Cells with the SUBTOTAL Function

You can use the Google Sheets filter and the subtotal function to count the colored cells in Google Sheets

This example has a color-coded employee table. Where the employee has met the specific goal criteria it is highlighted in green.

Details about the Employee ID in Column A, First Name in Column B, Last Name in Column C, and Salary in Column D in the above sample data table.

Suppose you want to count the number of records in green color in the data set.

Follow the below steps to create a filter and count only the green-colored cells in your data

1. Select your range of data including the column headers.
2. Click the Data menu.
3. Select the Create a filter option.

1. Click on the Filter icon which appears beside the column headers. You can use any column header to filter the data as long as it contains the colors you’re trying to count.
2. Choose the Filter by color option from the menu.
3. Select the Fill color option.
4. Select the color which you need to count.

Google Sheets filter view will display only the green-colored rows in the range of cells.

Now you can count the visible cells using the SUBTOTAL function. After you selected the color and applied the filter

= SUBTOTAL ( 103, B2:B11 )

The above formula counts only the visible cells in the range of cells B2:B11.

The first parameter value 103 in the above formula specifies the subtotal function to perform the COUNTA function as the subtotal type.

It returns the count of rows visible in the range B2:B11. Since this range is filtered on the green cells it returns the count of the green colored cells.

Follow the below steps to remove the filter in Google Sheets.

1. Click on the Filter icon in the column heading.
2. Choose Filter by color from the menu.
3. Select None in the submenu.

Notice the SUBTOTAL function will now return the count of all the rows since they are all visible!

Count Colored Cells with an App Script Custom Function

There is no function available in Google Sheets that will allow you to count cells based on the cell fill color.

But the good news is you can create your custom function for this purpose using apps scripts!

💡 Tip: You can read more in this post about Google Sheets Apps Scripts.

Go to the Extensions menu and select the Apps Script option to open the apps script code editor window.

/**
* This function will return the count of cells in the given range of cells based on a given color
* @param1 range - This is the range of cells to count.
* @param2 cell - This is a cell reference with the color to count.
* @returns the count of coloured cells 
* @customfunction
**/
function COUNTCOLOR(range,cell) {
  //Get the formula from the current cell
  var currRange = SpreadsheetApp.getActiveRange();
  var currSheet = currRange.getSheet();
  var currCellFormula = currRange.getFormula();

  //Parse the first parameter and convert to a range reference
  var para1 = currCellFormula.match(/\((.*)\,/).pop();
  var countRange = currSheet.getRange(para1);
  var countRangeBG = countRange.getBackgrounds();

  //Parse the second parameter and convert to a range reference
  var para2 = currCellFormula.match(/\,(.*)\)/).pop();
  var goalCell = currSheet.getRange(para2);
  var goalCellBG = goalCell.getBackground();
  
  var counter = 0;
  
  for (var i=0;i < countRangeBG.length; i++) {
    for (var j=0;j < countRangeBG[0].length; j++) {
      if (countRangeBG[i][j] === goalCellBG) {
        counter++;
      }
    }
  }
  return counter;
}; 

Copy and paste the above script into your Google Sheets code editor and press the Save button.

You will now be able to use the COUNTCOLOR function in your workbook.

= COUNTCOLOR ( range, cell )

• range is a range of cells that you want to count.
• cell is a single cell reference to a colored cell. This should contain the color you want to count.

Follow the below steps to use the custom function to count the green-colored cells in Google Sheets.

= COUNTCOLOR ( B2:B11, A13 )

Place the above formula anywhere on the sheet. Note that you passed the range B2:B11 to the first parameter range and the cell A13 to the cell parameter.

The cell parameter must contain the background color which you are trying to count.

The function returns the number of green-colored cells in the data set.

Count Colored Cells with an Add On

Another way to count colored cells is with the use of an add-on.

Add-ons are third-party apps that will add extra functionality to your Google Sheets.

Follow the below steps to add the Ablebits Power Tools add-on in Google Sheets

1. Select the Extensions menu from the main menu bar
2. Select the Add-ons option.
3. Choose the Get add-ons option.

This will open the Google Workspace Marketplace where you can search for and install various add-ons.

4. Type Power Tools in the search apps text box in the google workspace marketplace dialog box.
5. Select the Power Tools add-on.

The above steps allow for the installation of Power Tools.

6. You can click the Install button.

Google workspace marketplace asks your permission to install add-on.

7. Press the Continue button to install.

After successful installation, it will pop up a dialog box Where to find Power Tools

You can see the above Power Tools toolbox on the right side of your browser screen. 

Follow the below steps to start Power Tools if it is closed in your Google Sheets

1. Select the Extensions menu.
2. Click Power Tools.
3. Choose the Start option to start Power Tools add-ons.

Now, you can see the Power Tools toolbox on the right side of your browser screen. 

Follow the below steps to count green colored cells in the range of B2:B11 in the Google Sheets with Power Tools Add on.

1. Click the Σ (sigma) symbol to calculate cells based on their colors.  
2. Select Function by colour option in the menu.

4. Enter the range of cells that you want to count in the Select range input box.
5. Click the color picker icon to open the Function by color dialog box.

6. Enter the cell address of a cell containing the color you want to count in the input box.
7. Press the OK button.

8. Select COUNTA (text) function in the Use function drop-down list.
9. Type the cell address where you want to place the results of the calculation in the Paste results to the input box.
10. Press the Insert Function button.

= COUNTA ( valuesByColor ( "lime", "black", B2:B11 ) )

This will insert the above formula into your selected cell that counts the colors. The formula uses valuesByColor a custom function created by the add-on to count the colors.

In this example, the formula is counting all the cells with a "lime" fill color and "black" font color in the range B2:B11.

Conclusions

Color coding data is not a recommended practice as it can be hard to perform further analysis based on colors. But if you already have a color-coded data set, it is possible to count your data based on color.

The SUBTOTAL function method to count colored cells in Google Sheets depends on the visible cells in the data range. Using this method, you cannot count more than one color at a time, since you can only filter one color at a time.

You can use both an App Script Custom Function or the Ablebits Power Tools add-on to count more than one color at a time.

Do you know any other methods to count colored cells in Google Sheets? Let me know in the comments below!