Often you may want to count the number of cells that are conditionally formatted in Excel.
Fortunately this is easy to do by using the Filter function combined with the SUBTOTAL formula in Excel.
The following example shows how to do so.
Example: Count Conditionally Formatted Cells in Excel
Suppose we have the following dataset in Excel where we have used conditional formatting to add a red background to each value in the points column with a value greater than 30:
Suppose we would like to count the number of cells in this dataset that have been conditionally formatted.
To do so, we need to first filter the dataset to only show the cells with conditional formatting.
Right click on any cell that has conditional formatting, then click Filter from the dropdown menu, then click Filter by Selected Cell’s Color:
The dataset will automatically be filtered to only show the rows that have a red background in the points column:
Lastly, type the following formula into cell B12 to count the number of conditionally formatted rows:
=SUBTOTAL(102, B5:B9)
The formula returns a value of 3.
This tells us that 3 cells from the original dataset have conditional formatting.
Note: The value 102 in the SUBTOTAL function is a shortcut for finding the count of a filtered range of rows.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: Apply Conditional Formatting to Overdue Dates
Excel: Apply Conditional Formatting if Cell is Between Two Dates
Excel: Apply Conditional Formatting Based on Multiple Conditions
