Excel COUNT Coloured Cells

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.
• Filters
• The SUBTOTAL Function

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:

=SUBTOTAL(102,C9:C35)

=9

Or if I wanted to SUM the salaries >$1m I could use this SUBTOTAL formula:

=SUBTOTAL(109,C9:C35)

=$54,491,666

The syntax for the SUBTOTAL function is:

=SUBTOTAL(function_num, ref1,ref2,…)

In English:

=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.

Notes:

1. The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.
2. 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.

Share This

Tweet

Please share this or leave a comment and I'll make sure you get a personal reply.