SUMPRODUCT Within certain Times

  • Thread starter Thread starter Brendon
  • Start date Start date
B

Brendon

I would like to count the number of people that meet the following
criteria:

Arrival Time (12:00am-12:00pm)
Visit Type: (A or W or D)
Wait Time (between 11-20 mins)

Here's what I have so far:
=SUMPRODUCT(--(PTARRTIME<0.5)*((TIME(0,11,0)<PTWTTIME<=(TIME(0,20,1)))*
(VISITTYPE="A")))

This will calculate all patients who arrived in the morning, were an
appointment patient, and waited between 11-20 minutes.

Any assistance is greatly appreciated.
 
If you enter the time the patients waited in regular number format using
only minutes like 10, 15, 25 etc and the arrival time is entered as regular
time like 10:35 etc then you can use this


=SUMPRODUCT(--(PTARRTIME<"12:00"),--(PTWTTIME>=11),--(PTWTTIME<=20),--(VISITTYPE="A"))


if you enter the wait time in time format like 00:10, 00:15, 00:25 etc and
the arrival is in time format then you can use


=SUMPRODUCT(--(PTARRTIME<"12:00"),--(MINUTE(PTWTTIME)>=11),--(MINUTE(PTWTTIME)<=20),--(VISITTYPE="A"))

--


Regards,


Peo Sjoblom
 
Change that to




=SUMPRODUCT(--(PTARRTIME<--"12:00"),--(PTWTTIME>=11),--(PTWTTIME<=20),--(VISITTYPE="A"))


and


=SUMPRODUCT(--(PTARRTIME<--"12:00"),--(MINUTE(PTWTTIME)>=11),--(MINUTE(PTWTTIME)<=20),--(VISITTYPE="A"))

--


Regards,


Peo Sjoblom
 
So you are using 01:30 to display 90 minutes of wait time?

This should take care of that



=SUMPRODUCT(--(PTARRTIME<--"12:00"),--(PTWTTIME>=--"01:00"),--(PTWTTIME<=--"01:30"),--(VISITTYPE="A"))



Will count A with a wait time of 60-90 minutes

--


Regards,


Peo Sjoblom


OK, that works until I get to a wait time that is over 1 hr. Then it
doesn't compute.
 
Back
Top