how to count cells of a certain color in google sheets

How to Count Cells of a Certain Color in Google Sheets: Easy Guide

By Kevin Davis
AD

Managing color-coded data in Google Sheets is a common practice for project tracking, status reporting, and visual analysis. While you can easily see the colors, extracting a specific count often feels manual. This guide provides precise methods to count cells of a certain color, empowering you to automate your data analysis and save valuable time.

Understanding the Challenge

The standard COUNT functions in Google Sheets ignore cell background colors, focusing only on text or numeric values. This creates a roadblock when your data visualization relies on color. To solve this, you must leverage Google Apps Script, a powerful cloud-based scripting language that integrates directly with Sheets. By creating a custom function, you bridge the gap between visual formatting and quantitative analysis.

Method 1: Using Google Apps Script

This is the most robust and reliable approach. It involves writing a script that loops through a range, checks the background color of each cell, and returns a total. Follow these steps to implement it.

AD
3 ways to count colored cells in Google Sheets (including a free script)
3 ways to count colored cells in Google Sheets (including a free script)
How to Automatically Change Cell Color in Google Sheets
How to Automatically Change Cell Color in Google Sheets
How to count or sum cells based on cell color in Google sheet?
How to count or sum cells based on cell color in Google sheet?

Creating the Custom Function

Open your Google Sheet and navigate to Extensions > Apps Script. Delete any placeholder code and paste the following function. This script, named countByColor, requires two arguments: the color to search for and the range to search within.

function countByColor(color, range) {
  var activeRange = SpreadsheetApp.getActiveSpreadsheet().getRange(range);
  var activeSheet = activeRange.getSheet();
  var colorCells = activeRange.getBackgrounds();
  var targetColor = color.toUpperCase();
  var count = 0;

  for (var i = 0; i < colorCells.length; i++) {
    for (var j = 0; j < colorCells[i].length; j++) {
      if (colorCells[i][j] == targetColor) {
        count++;
      }
    }
  }
  return count;
}

Save the project with a relevant name, such as "Color Counter". Once saved, you can use your new function directly in any cell like a standard formula.

How to add a Calendar into a cell in Google Sheets #googlesheets #tutorial #spreadsheet
How to add a Calendar into a cell in Google Sheets #googlesheets #tutorial #spreadsheet
Count + Sum Cells based on Cell Colour in Excel - How To - PakAccountants.com
Count + Sum Cells based on Cell Colour in Excel - How To - PakAccountants.com
Count & sum cells by color in Google Sheets
Count & sum cells by color in Google Sheets

Applying the Function in Your Sheet

After the script is deployed and authorized, return to your sheet. To count cells with a specific background color, use the following syntax:

=countByColor("yellow", "B2:B15")

Replace yellow with the color name or hex code (e.g., "#FFFF00") and B2:B15 with your target range. The function will scan the specified range and return the exact number of cells matching that color.

AD

Method 2: The Helper Column Technique

If you prefer to avoid scripting, a helper column offers a transparent, formula-driven alternative. This method works by converting the cell color into a text value that standard functions can read.

How to Change Your Google Sheets Theme Color
How to Change Your Google Sheets Theme Color
How to get the HTML color codes in Google Sheets - Plus every color in the color picker!
How to get the HTML color codes in Google Sheets - Plus every color in the color picker!
Struggling to add Alternating Colors in Google Sheets
Struggling to add Alternating Colors in Google Sheets
How to Automatically Change Cell Color in Google Sheets
How to Automatically Change Cell Color in Google Sheets
Google Sheets - Sum or Count Values Based on Cell Color
Google Sheets - Sum or Count Values Based on Cell Color
How to Sum and Count Cells by Color in Excel?
How to Sum and Count Cells by Color in Excel?

Implementing the TEXTJOIN Formula

In an empty column next to your data, enter this formula to generate a text string representing the color of the adjacent cell:

=ARRAYFORMULA(TEXTJOIN(",", TRUE, IF(ROW(B2:B15)=ROW(B2), REGEXEXTRACT(CELL("address", B2:B15), "\w+"), IF(B2:B15="",,REGEXEXTRACT(CELL("color", B2:B15), "#\w+")))))

This complex formula pulls the color code for each cell. While setup is intricate, the resulting column allows you to use COUNTIF to tally specific values easily.

AD

Counting the Extracted Values

Once the helper column displays color codes, you can count them using a simple formula. For example, to count yellow cells (represented by a specific hex code), use:

Google Sheets - Sum or Count Values Based on Cell Color [Improved]
Google Sheets - Sum or Count Values Based on Cell Color [Improved]
Using COUNTIF with Colors
Using COUNTIF with Colors
How to change Cell Color in Google Sheets
How to change Cell Color in Google Sheets
Uncover Hidden Excel Powers: Sum & Count Cells by Color!
Uncover Hidden Excel Powers: Sum & Count Cells by Color!
How to Use Conditional Formatting Based On Another Cell in Google Sheets
How to Use Conditional Formatting Based On Another Cell in Google Sheets
Google Sheets Aesthetic
Google Sheets Aesthetic
VBA Code To Count Cells By Color - Easy Way -With 2 Codes Example
VBA Code To Count Cells By Color - Easy Way -With 2 Codes Example
How To Apply A Button To Clear Specific Cells In Google Sheets in 2024
How To Apply A Button To Clear Specific Cells In Google Sheets in 2024
How To Create A Filter in Google Sheets Mobile?
How To Create A Filter in Google Sheets Mobile?
How to Highlight Duplicates in Google Sheets
How to Highlight Duplicates in Google Sheets
an image of a calendar with dates and times listed in the top right hand corner
an image of a calendar with dates and times listed in the top right hand corner
Count The Number of Cells With Specific Cell Color By Using VBA - Office
Count The Number of Cells With Specific Cell Color By Using VBA - Office
Google Sheets: Alternate Colors - Teacher Tech with Alice Keeler
Google Sheets: Alternate Colors - Teacher Tech with Alice Keeler
excel how to highlight the active cell
excel how to highlight the active cell
=COUNTIF(Helper_Column_Reference, "*FFFF00*")

This approach is less dynamic than the script method but provides a clear audit trail of how the data is being evaluated.

Practical Tips and Best Practices

To ensure accuracy and efficiency, consider these key points. Always test your count on a small, known dataset to verify the script is detecting the correct shades. Note that the script checks the exact background color, so slight variations in shades will be counted separately. For large datasets, script execution might take a few seconds; be patient while it processes.

AD

Additionally, maintain consistency in how you apply colors. Use conditional formatting rules rather than manual fills to guarantee uniformity. If you are sharing the sheet, inform collaborators that custom functions require authorization to run for the first time.

Troubleshooting Common Issues

Occasionally, the script may return zero counts even when colors are present. This usually stems from range errors or incorrect color name formatting. Ensure the range specified in the formula matches the actual data range exactly, including the sheet name if necessary.

If the color is not recognized, try using the standard color name (e.g., "red", "green", "yellow") instead of a hex code. Furthermore, remember to install the trigger for the script if you want it to run automatically on form submissions or time-driven intervals, although for manual counting, the direct formula suffices.

AD