Excel countif text color is red

Watch Video – How to Count Colored Cells in Excel

Wouldn’t it be great if there was a function that could count colored cells in Excel?

Sadly, there isn’t any inbuilt function to do this.

BUT..

It can easily be done.

How to Count Colored Cells in Excel

In this tutorial, I will show you three ways to count colored cells in Excel (with and without VBA):

1. Using Filter and SUBTOTAL function
2. Using GET.CELL function
3. Using a Custom Function created using VBA

#1 Count Colored Cells Using Filter and SUBTOTAL

To count colored cells in Excel, you need to use the following two steps:

• Filter colored cells
• Use the SUBTOTAL function to count colored cells that are visible (after filtering).

Suppose you have a dataset as shown below:

There are two background colors used in this data set (green and orange).

Here are the steps count colored cells in Excel:

1. In any cell below the data set, use the following formula: =SUBTOTAL(102,E1:E20)
   
2. Select the headers.
3. Go to Data –> Sort and Filter –> Filter
   how to find colored cells in excel
   how to count colored cells in excel conditional formatting