Count with three conditions

  • Thread starter Thread starter poo-gien
  • Start date Start date
P

poo-gien

Hi everyone
Who can help me to solve a problem where three conditions
are to be taken into account?
I have a worksheet with datas as shown in the example
below:

Date Day of the week Waiting time

3/15/2004 2 1:15
3/16/2004 3 1:45
3/20/2004 6 3:14
3/21/2004 1 2:13

I would like to count how many patients there are with
waiting time of >= 1:00 and <=2:00 for each day of the
week, knowing that the countif formula can't be used as it
does not allow the intersection of two conditions.
Thanks a lot to whoever can give me an answer
Poo-Gien
 
=SUMPRODUCT(--(DayOfTheWeekRange=X2),--(WaitingTimeRange>=Y2),--(WaitingTime
Range<=Z2))

where X2 houses a weekday number like 2, Y2 a duration value like 1:00, and
Z2 a duration value like 2:00 with Y2 <= Z2.
 
Hi everyone
Who can help me to solve a problem where three conditions
are to be taken into account?
I have a worksheet with datas as shown in the example
below:

Date Day of the week Waiting time

3/15/2004 2 1:15
3/16/2004 3 1:45
3/20/2004 6 3:14
3/21/2004 1 2:13

I would like to count how many patients there are with
waiting time of >= 1:00 and <=2:00 for each day of the
week, knowing that the countif formula can't be used as it
does not allow the intersection of two conditions.
Thanks a lot to whoever can give me an answer
Poo-Gien
Click to expand...

First of all, I'm not sure about your layout since if 3/21 (a Sunday) is Day 1
of the week, then 3/20 (a Saturday) should be Day 7.

Does each line represent the waiting time for one patient?

If so, then the *array-entered* formula:


=SUM((Day_of_the_week=2)*(Waiting_time>=TIME(1,0,0))*(Waiting_time<=TIME(2,0,0)))

should do it for Day 2 of the week.

However, the Day of the Week column is superfluous in the formula. One could
also use the ARRAY formula:



=SUM((WEEKDAY(Date)=2)*(Waiting_time>=TIME(1,0,0))*(Waiting_time<=TIME(2,0,0)))



To *array-enter* a formula, hold down <ctrl><shift> while hitting <enter>. XL
will place braces {...} around the formula.


--ron
 
Thanks again for sending me such a quick answer. I hope to
try it out as soon as possible.

Best regards
PooGien
 
Back
Top