Excel 2016 : How to Count Color Cells

Counting Colored Cells in Excel 2016

[hzi]

Sorry, wrong title. This is not a count by color. It's a count by setting a filter, which is something totaly different

[Ryan-dclj]

This is counting the number of cells in the filter, not those highlit. Is there a way to count based on format?

[SSSoM]

This is fantastic, thank you for this

[shakirtas]

if the cells are empty formula does not work?

[ttbloodlustttangela]

I'm using Office 365 and in Excel application when I filter by color, it simply puts the colored cells at the top and does not hide the rest, thus the total doesn't reflect only the colored rows correctly. Any suggestions?

[vladanvukosavljevic]

You're a genius ... thank you so much :)

[NerdsmithTV]

Is there a way to retain the color count while the cell is not filtered? If I need to keep a running total using conditional formatting, this wouldn't really work for me, having to constantly pull up a filter.

[ediemilov]

This just saved me at least 3 hours of manually counting cells! Thank you! :)

[matthewhuffine]

I had better luck with count option 3 rather than 2

[TheRooster]

You've entered too few arguments for this function.

[ekariona]

Help me please, it says "You've entered too few arguments for this function."

[niteshranjanjha]

how i do count 3 different colors in 1 sheet

[yrot]

thanks for sharing this subtotal count method...
if someone doesn't want to use filtering color cells, here's another way...
alternatively, open the VB window by pressing ALT + F11
paste this code below to the VB window, hit save button, exit the VB window.
Select cell to store the count for color cells, type =GetColorCount(range, cell contains color)

➡VBA Code:
Function GetColorCount(CountRange As Range, CountColor As Range)
Dim CountColorValue As Integer
Dim TotalCount As Integer
CountColorValue =
Set rCell = CountRange
For Each rCell In CountRange
If rCell.Interior.ColorIndex = CountColorValue Then
TotalCount = TotalCount + 1
End If
Next rCell
GetColorCount = TotalCount
End Function

[market]

This is using the filter to count, which is lacking.

[edisondominguez]

useless, this is not count and you are only filtering to count, which will be hard if one has several color at the same time

[danieldanieldadada]

This is useless. People want to count by color without using a color filter.