Count cells with conditional formatting

[desert rat]

Hi All,

I've searched multiple sites looking for ideas on how to count cells that have a specific colour based on conditional formatting - but there seems to be no way to do this.

I have a VBA to count cell colours that I change manually and this works fine, but I am after some assistance in working out a way to count the cell colours that have changed based on conditional formatting (2 separate formulas). See attached spreadsheet.

I was thinking of adding helper columns that would equal 1 or 0 if the formulas were true or false but not sure if this is even possible.

Open to ideas/suggestions on how this could be achieved.

Thanks

 

[Hui]

B27: =SUMPRODUCT((B3:B23<>"")*(IF(DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY()))>(B3:B23),1,FALSE))) Ctrl+Shift+Enter
copy across

B26: =SUMPRODUCT((B3:B23<>"")*(IF(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))<(B3:B23),1,FALSE))) Ctrl+Shift+Enter
copy across

 

[desert rat]

B27: =SUMPRODUCT((B3:B23<>"")*(IF(DATE(YEAR(TODAY())-3,MONTH(TODAY()),DAY(TODAY()))>(B3:B23),1,FALSE))) Ctrl+Shift+Enter
copy across

B26: =SUMPRODUCT((B3:B23<>"")*(IF(DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))<(B3:B23),1,FALSE))) Ctrl+Shift+Enter
copy across

Thanks Hui.

The formula works fine for counting those cells that are red, but doesn't work for those that are yellow. I have tried to manipulate the formula but no luck.

I have attached an updated version of the Test spreadsheet with your formulas included as well as the formulas I am using for the conditional formatting.

Any further suggestions?

Thanks again.