I need to count cells with conditional format

F

Florian

Hi, I have a table with cells that have conditional formats.
If one condition is met then that cell become red. Not all cells are red on a column.
I want to count only the red cells on that column. Is it possible?
Thx.
 
S

Simon Shaw

Have you tried the COUNTIF function, using the same criteria set in the conditional formatting?

Otherwise, the only way I know of to count red cells is through a macro.

Simon Shaw
www.accounttech.ca
 
F

Frank Kabel

Hi Florian
though i think Bob Phillips/Harlan Grove posted a solution for checking
the conditional format conditions it would be much easier just to
duplicate your conditions in a formula. Using COUNTIF or SUMPRODUCT

Note: you also can't use a macro to count the red cells as the
conditional format color does not change the colorindex property of a
cell.
 
F

Florian

I manage to get what I wanted with a complicated "uncompressed" formula (like a DivX compared with AVI uncompressed). The formula was something like this: =SUM(AND(G7=$F7)+AND(G8=$F8)+AND(G9=$F9)+AND(G10=$F10)+AND($F11=G11)+AND($F12=G12)+AND($F13=G13)+AND($F14=G14)+AND($F15=G15)+AND($F16=G16)+AND(G17=$F17)+AND(G18=$F18)+AND(G19=$F19)+AND(G20=$F20)+AND(G21=$F21)+AND(G22=$F22)+AND(G23=$F23)+AND(G24=$F24)+AND(G25=$F25)+AND(G26=$F26)+AND(G27=$F27)+AND(G28=$F28)+AND(G29=$F29)+AND(G30=$F30)+(AND(G32=$F32)*AND(G32<>0))+AND((G33=$F33)*AND(G33<>0))+AND((G34=$F34)*AND(G34<>0))+AND((G35=$F35)*AND(G35<>0))+AND((G36=$F36)*AND(G36<>0))+AND((G37=$F37)*AND(G37<>0))+AND((G39=$F39)*AND(G39<>0))).
Ugly, isn't it? But was that I was looking for. Lucky me I just needed only 30 rows to count, otherwise I'm pretty sure that this formula will not work if it was larger.
Anyway thanks for your advices. Macros I don't know to use.
 
Top