COUNT using coloured cells
Posted by Deirdre on August 30, 2001 12:06 PM
My dilema:
A B
5 6
6 9
3 8
Columns A and B are not related but simply sums of other cells in their resoective rows.
I have conditional formatting set if B > A then it is to colour the cell red and the font yellow.
What I would like to do is COUNT the number of Red Cells in column B.
I've seen some examples on this message board, but they are for adding the values I believe in the coloured cells. I simply just want to count the number of occurrences in a column range.
Your assistance is much appreciated.
~Deirdre
Posted by Aladin Akyurek on August 30, 2001 12:16 PM
It's good that you stated the condition to color a given cel red. You can use that condition to count the cells of interest.
=SUMPRODUCT((B1:B100>A1:A100)+0)
will give you the count you're looking for.
Aladin
Posted by Deirdre on August 30, 2001 12:41 PM
Thank you Aladin --Your guru-ness has come through again. I have one more thing to add, and it seems my efforts are once again futile.
I J
3:45 4:15
5:00 5:45
4:23 2:02
With your formula I am able to count the occurrences J > I. However, I would also like ti make a new calculation that counts the number of occurrences J > I AND I <"4:00".
Here is my feeble attempt:
=SUMPRODUCT((J2:J151>I2:I151))*(I2:I151<"4:00")+0
It returns a ##### error.
Sadly,
Deirdre
Posted by Deirdre on August 30, 2001 12:52 PM
I DID IT!
=SUMPRODUCT((J2:J151>I2:I151+0)*I2:I151<"4:00"+0))
:: snoopy dance ::
