count colored cells in excel

[Maddog]

Need a way to count the number of colored cells in a column that are filled
with a specific color via conditional formatting.

Found a VBA script on the internet for a ColorFunction code that will count
colored cells if manually filled. Doesn't work for those filled via
conditional formatting.

Any help would be appreciated.

Thanks!

 

[Roger Govier]

Hi

You need to count the cells matching the condition you set in your CF
formula.
Post your CF formula, and perhaps we can help you.

 

[David Biddulph]

Look at the condition you have used for your CF, and use that in your
COUNTIF condition.

 

[Maddog]

Have quite a few. For just one column there are 6 separate conditions where
3 will return the same fill color. I've listed them below with the fill
color after each rule.

Cell Value >= TODAY()+1095 Red

Cell Value between TODAY()+61 and TODAY()+90 Green

Cell Value = 0 Red

Cell Value between TODAY()+31 and TODAY()+60 Blue

Cell Value between TODAY()+1 and TODAY()+30 Yellow

Cell Value <= TODAY() Red

 

[Roger Govier]

Hi

Assuming your dates are in column A
For Red
=SUMPRODUCT(--(A1:A10>=TODAY()+1095))+SUMPRODUCT(--(A1:A10<TODAY())*(A1:A10<>""))
The 0 Value will get picked up along with <Today() as 0 will always be less
than today()

For Green
=SUMPRODUCT((A1:A1000>=TODAY()+61)*(A1:A1000<=TODAY()+90))
For Blue
=SUMPRODUCT((A1:A1000>=TODAY()+31)*(A1:A1000<=TODAY()+60))
for Yellow
=SUMPRODUCT((A1:A1000>=TODAY()+1)*(A1:A1000<=TODAY()+30))

 

[Maddog]

Roger,

THANKS!

Green, blue, and yellow worked as written. Red ignored the cells that are
empty. The cell = 0 CF command colors those red. Perhaps 0 and null are not
the same thing for all commands? Removed the *(A1:A10<>"") at the end of the
Red command and it seems to work now.

I'm not much of a formula guy, but do try to understand how they work. What
was the *(A1:A10<>"") at the end of the Red line supposed to do?

As the * is a multiplication command, I don't understand why the formulas
don't multiply the number of blocks and return erronous results.