From time to time I get asked; “how can I count cells formatted in a particular colour”. Aka 'color' if you’re from the U.S.
Well, there are some VBA solutions around, but today I’d like to look at a solution that’s fairly straight forward, and uses the tools already available in Excel.
Those tools are:
- Conditional Formatting – this is used to colour the cell/font. Although you could manually colour the data if you want.
- The SUBTOTAL Function
Excel Count Coloured Cells
Below is my data. I’ve set a Conditional Format to highlight cells in column C that are >$1m, and now I’d like to COUNT them.
I’ve also inserted Filters in row 8.
To insert a filter click on the row you want to use as your column labels > Data tab of ribbon > Filter.
Now I need to filter by colour.
Click on the filter button for Salary > Filter by Color > Filter by Cell Color, or since my font is coloured too I could 'Filter by Font Color'.
Now my data looks like this:
Note how the row numbers are blue and the numbering skips to show that some rows are hidden.
Now I can use the SUBTOTAL function to count only those cells that are visible. Like this:
Or if I wanted to SUM the salaries >$1m I could use this SUBTOTAL formula:
The syntax for the SUBTOTAL function is:
=SUBTOTAL(what type of total do you want, what range/s do you want to subtotal)
The function_num specifies the type of function you want to use. Here are what’s available in SUBTOTAL function candy store:
The trick is that when used with a filtered list the SUBTOTAL function will only COUNT, SUM, AVERAGE etc. those cells that aren’t hidden/filtered.
- The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.
- If I clear my filters the SUBTOTAL function will COUNT/SUM all of the data in the range C9:C35.
For more on how the SUBTOTAL function works.
Count Coloured Cells UDF
Another way to count coloured cells is with the UDF: https://www.myonlinetraininghub.com/count-sum-and-average-colored-cells