counting cells formatted with fill color

[marcuss3]

Is there any way to count cells in a single column that have a fill format? Like sum all yellow cells in column A...

[roki4]

Set C1 to the color you want to sum. Then use this: =ColorFunction($C$1,$A$1:$A$12,TRUE).

Let me know if it works.

Alternately, have a look at this web site: http://www.cpearson.com/excel/colors.aspx

[marcuss3]

doesn't work roki4, I'm getting #NAME? error

[mo245]

Yes, I have also tried =SUMPRODUCT(--(ColorIndex(A1:A100)=3)), but the same result: #NAME? error...

[npamcpp]

Color is not data.

Excel has no functionality to sum our count by color.

It can filter by colour, though, but in general this is the backwards approach. Use a column to define a parameter. Then use conditional formatting based on the parameter. The color is for visual effect only. You can easily use Sumif() or Countif() to sum or count the parameters.

[martindwilson]

ColorFunction is not a native excel function,it looks like a custom udf.
what you can do is filter by colour then use the subtotal() function to count the filtered cells either the count or counta option within subtotal. if you are counting coloured cells that are blank tho' use a helper column just fill it to the end of your data with 1 then you can subtotal count that when you've filtered it.

[mo245]

Beauuutiful!

Many thanks!

[martindwilson]

oh here is a example

[marcuss3]

Thanks a million martindwilson, it works though I wish excel had that ability with a simple function, but for now this will do, thanks again