Count If

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I posted this question Friday but I don't think that it was very clear.

I have a spreadsheet that has admission times to different floors. Our
floors range from 3-7 and also includes surgery. The time I am wanting to
count is how long it took the patient to get to the floor once they called
for a bed number. There will be over 100 admissions for the month that I
need numbers for.

The floor number is in column I, the time is in column S. I need a
breakdown of each floor - how many were less than 00:45, 00:46 - 1:00, 1:01 -
1:30 and greater then 1:30.

I hope that this makes sense.
 
One way:

Assume that the desired floor number is in Z1:

AA1: =SUMPRODUCT(--(I1:I200=Z1),--(S1:S200<=TIME(0,45,0))
AA2: =SUMPRODUCT(--(I1:I200=Z1),--(S1:S200>TIME(0,45,0),
--(S1:S200<=TIME(1,0,0))

AA3: =SUMPRODUCT(--(I1:I200=Z1),--(S1:S200>TIME(1,0,0),
--(S1:S200<=TIME(1,30,0))

AA3: =SUMPRODUCT(--(I1:I200=Z1),--(S1:S200>TIME(1,30,0))
 
The general form
=sumproduct(--($I$2:$I$150=3),--($S$2:$S$150>TimeValue("00:45")),--($S$2:$S$
150<=TimeValue("01:00")))
Each hard coded entry can be replace by a cell reference to a cell
containing that data

=sumproduct(--(Data!$I$2:$I$150=A2),--(Data!$S$2:$S$150>B2),--(Data!$S$2:$S$
150<=C2))

On the sheet with the formula
A2: 3
B2: 00:45
C2: 01:00

Regards,
Tom Ogilvy
 
Back
Top