How To Count Colored Cells In Google Sheets

Last Updated on October 26, 2021 by Jake Sheridan

In this tutorial, you will learn how to count colored cells in Google Sheets.

When working in Google Sheets, it’s common practice to use cell colors to designate status or other important information about the data in your spreadsheet. Because of this, you may find yourself wanting to know how many cells have a certain color. While Google Sheets unfortunately doesn’t have a specific feature to count cells with a particular color, don’t rush off to start manually counting those cells. There’s a workaround that can help you get the count of colored cells without manually counting them yourself

Follow the steps below to see how this works

Adding a Filter

The first step to counting colored cells is to add a filter. We’ll use the filter to quickly hide all cells except cells with the color of interest, and then use a formula to count the visible cells

Step 1

Select the range in which you want to count colored cells, including headers, and open the Data menu, then select Create a Filter

Step 2

Filter icons will appear beside each header name. Click on the filter icons to open the filter menu and choose Filter By Color, then Fill Color, and finally the color you want to count

Step 3

The filter view will update to show only rows where cells in that column are the selected color

Step 4

You can remove the filter by opening the filter menu again and None under Filter by Color

Counting Colored Cells

Once you’ve added the filter and selected the color you want to count, follow these steps to count cells with that color. Note that in this example all cells in a given row have the same color, but this method could be used for a single column if the cell colors differ between columns

Step 1

Ensure you’ve followed the filtering steps above and have filtered by color to display only the cells with the color of interest

Step 2

Unlike many other Google Sheets functions, the SUBTOTAL function looks at only visible cells, so it can be used to count the colored cells when filter by color is applied. Type the following formula into the header row of your spreadsheet so it won’t be hidden or shifted when you modify filters: “=SUBTOTAL(103, A2:A)

In this formula, the data you want to count is in the range A2:A, and we’re specifying the type of subtotal with the number 103, which means the SUBTOTAL function will perform COUNTA, which returns the number of values in the dataset

Step 3

The total number of visible cell values in the specified range will display and update as you change the filter to display different colors


Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to count colored cells in Google Sheets. Want more? Check out all the Google Sheets Tutorials.