Count If Formula

[Guest]

I need a formula that will figure the following:

If column C = 3, count all times < 00:45
If column C = 4, count all times < 00:45
If column C = 3, count all times > 00:46

Thanks.

 

[Bob Phillips]

Your description is a bit confused.You have different actions for the same
value (C=3). And where are the times, in a different column than C?

So, making many assumptions, perhaps

=SUMPRODUCT(--(ISNUMBER(MATCH(C1:C200,{3,4},0))),--(D1200<TIME(0,45,0)))+S
UMPRODUCT(--(C1:C200=5),--(D1200<TIME(0,46,0)))


--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Excel_Geek]

Denise,

I believe an array formula may be the answer you're after.

First, let me ask if you mean't...

If column C = 3, count all times < 00:45
If column C = 4, count all times < 00:45
If column C = *5*, count all times > 00:46

(You had C = 3 twice, which didn't make sense to me.)

If that is what you meant, I believe this will do what you ask:

{=SUM(IF(C1=3,IF(F3:F16>45/24/60,1,0),IF(C1=4,IF(F3:F16<45/24/60,1,0),IF(C1=5,IF(F3:F16>46/24/60,1,0)))))}

Where C1 is the column C value you name, and F3:F16 is the range of all
times, that are formatted as such 0:40, where the actual value (up in
the formula bar) is 12:40:00 AM.

The "...45/24/60..." parts of the formula help convert time to decimal
values.

To input an array formula (to get those "{" and "}" around it, you
simply type in the formula without them, then hold SHIFT+CNTRL when you
hit ENTER.

Let me know if you need help.