MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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 ::