In this tutorial, you will learn how to count colored cells in Google Sheets.
How to Count Coloured Cells in Google Sheets
Quick Navigation
Color-coding data in a spreadsheet can be a convenient way to quickly distinguish between different types of information.
But while this type of formatting can make visualizing data easier, it can also make it difficult to count the number of specific coloured cells.
Luckily, Google Sheets offers a few tricks that can help you quickly count coloured cells in your spreadsheet.
Using a combination of the filter option and the SUBTOTAL function, you can easily count the number of coloured cells in your dataset.
In this guide, we will show you how to use a filter and the SUBTOTAL function to count coloured cells in Google Sheets.
How to Count Cells of a Certain Color in Google Sheets
Here’s how to count cells of a certain color in Google Sheets.
Step 1
First, select the range with the coloured cells you wish to count.
In our example, we have 15 different values shaded either blue, yellow, or orange. We want to know how many cells there are for each color.
Step 2
In the Data menu, select the option “Create a filter”
Step 3
The selected range should now have a filter icon appear in the topmost cell. Click on the filter icon and select Filter by color > Fill Color then choose the color you want to count first.
In this example, we’ll start by counting all yellow cells in the range.
Step 4
We’ll then use the SUBTOTAL function to count all the yellow values in column A.
The formula =SUBTOTAL(103, A2:A) counts only the visible cells in the range A2:A.
The first parameter value 103 in the above formula specifies the subtotal function to perform the COUNTA function as the subtotal type.
We can change the Filter options to find the count of every other color we want to start counting.
Summary
This guide should be everything you need to count coloured cells in Google Sheets.
You may make a copy of this example spreadsheet to test it out on your own.