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:
- 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.
Rajesh Sinha says
Wonderful idea
Peter Buyze says
Your way to count coloured cells requires filtering the cells 1st. That is not really convenient if you keep adding rows to the table, or if you need to use the hidden rows for something else too. I find it cumbersome to have to filter & unfilter.
Microsoft suggests a combination of VBA & a formula. The macro code is as follows:
Function CountCcolor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
xcolor = criteria.Interior.ColorIndex
For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
CountCcolor = CountCcolor + 1
End If
Next datax
End Function
After that it is possible to use the =CountCCOLOR(range) formula.
Philip Treacy says
Hi Peter,
The idea behind this post was to provide a way to count colored cells without using VBA. This post provides a VBA solution.
The ColorIndex indicates where on the palette the color resides. This isn’t the same as the Color, so using this code may give you the same ColorIndex for colors that are actually different.
If you give a cell a color then run this code while selecting that cell
You’ll get the values for the Color and the ColorIndex.
If you now adjust the Red, Green or Blue components by 1, and run it again, the ColorIndex will be the same but the Color will be different. So using Interior.Color is correct.
Regards
Phil
Peter Buyze says
Hi Philip, thanks for your reply. I was under the impression that each colour has a unique index, but your 2nd paragraph indicates that is not the case, which does not sound logical to me. Nevertheless, in my use case I have 1 reference colour which I use to colour particular cells with, and the macro works fine like that. And I do not have to apply any filtering prior to counting my coloured cells, which was my issue.
Philip Treacy says
Hi Peter,
The ColorIndex refers to where on the palette the color resides. You use the numbers 1 to 56 to access these colors.
The Interior.Color is the actual color (RGC/HEX value). The ColorIndex and Color aren’t the same thing, and not every color has a different ColorIndex. If you run the code I provided you’ll see you can actually change the color but get the same ColorIndex.
If you are using colors off the palette to fill your cells then everything will still work fine for you as you are using that palette color as a reference and checking the other cells for it.
Cheers
Phil
Balaji says
This is really good … but what in case if I need to put formula to automate the task based on the output of this count.
Mynda Treacy says
Hi Balaji,
I’m sorry I don’t understand your question. Perhaps you’d like to send an example of what you’re trying to do via the help desk.
Kind regards,
Mynda.
Ashutosh Bhatnagar says
How can We count the Cell based on color.
Mynda Treacy says
Hi Ashutosh,
Filter your column on the colour you want to count and then use the SUBTOTAL function number 103 to count cells containing data:
=SUBTOTAL(103,your range)
I hope that helps.
Kind regards,
Mynda.
Carolyn Rigg says
REALLY impressed by the explanation given above on how to count colour-filtered cells using the ‘visible cells’ option. I have been seeking this solution for ages! And it’s so simple! 🙂
Mynda Treacy says
🙂 Thanks, Carolyn. Glad to have helped.