background color count

[cmccabe]

I am using the following formula and getting a #NAME returned

I just need the count of the red background color cells.

=SUMPRODUCT(--(ExtCell("backgroundcolor",$E$2:$E$13,1)=3))

where E2:E13 have the values and only the red background (=3) is needed. If the green and yellow background colors where needed what do I set the = to? Thank you.

 

[ZVI]

ExtCell is UDF (User Defined Function).
For more details see this

For yellow use: =6, for green =10

 

[cmccabe]

Is there a better way to count the red cells? Thanks.

 

[ZVI]

You can use Conditional Formatting (CF) colors based on the cells values of addition column.
Then just count that aux column values instead of the colors.
For example, values of column D wil be 0 for none color of E cell, 1 for green, 2 for yellow, 3 for red.
Set CF accordingly but count numeric values in aux column D, not the collors:
=COUNTIF(D1:D12,3) returns count of red colors in E