SUMPRODUCT Help

[Zb Kornecki]

Please help me write a SUMPRODUCT formula
I have 3 columns
A=arrival time
C=time 1
D=time 2
I would like column B to show a count of how many times Col A falls between
each C & D pair
Thank-you
Zb

 

[Max]

Assuming start times in col C, end times in col D
In B1: =SUMPRODUCT((A$1:A$100>=C1)*(A$1:A$100<=D1))
Copy down to the extent of paired times in cols C and D. Adapt the range in
col A to suit beforehand.

 

[Zb Kornecki]

I tried this but it It still is not giving me what I need. I'm tring to
count how many people are waiting whenever someone new arrives. I need to
compare each time in col A (Arrival) agaist each pair of times in cols b&c
and count the times that a falls between them. Thank-you Zb

 

[Max]

I tried this but it It still is not giving me what I need. I'm trying to
count how many people are waiting whenever someone new arrives. I need to
compare each time in col A (Arrival) agaist each pair of times in cols b&c
and count the times that a falls between them.

Think what was suggested earlier was as per your intents and should have
worked. Perhaps you might want to check your data in cols A, C and D for
consistency. These should all be real times.

If it's not a data prob, could you copy n paste some actual sample data &
the expected results in plain text, in response here?

 

[Max]

A further thought ..

Assuming start times in col C, end times in col D
In B1: =SUMPRODUCT((A$1:A$100>=C1)*(A$1:A$100<=D1))

Above presumes the start and end times in cols C and D are non-overlapping.

If they are overlapping, eg if in C12 you have:

7:00 AM 8:00 AM
8:00 AM 9:00 AM
etc

just adjust the upper limit, viz use instead in B1:
=SUMPRODUCT((A$1:A$100>=C1)*(A$1:A$100<D1))

 

[Zb Kornecki]

Max This is working only not quite the way I was hoping the # waiting colunm
should show of those that were currently in waiting status as each
individulal arrives.
Thank you for your time and help w/ this. i was thinking sum products but
maybe there is another way to do this. I'm open to suggestions.

zb

I did a copy an paste of a csv for an example
Arrival,#waiting,Start_wait,End_wait
07/05/2007 03:20 AM,0,,07/05/2007 05:15 AM
07/05/2007 03:37 AM,12,07/05/2007 07:20 AM,07/05/2007 04:36 PM
07/05/2007 04:07 AM,0,,07/05/2007 08:32 AM
07/05/2007 03:45 AM,0,,07/05/2007 01:50 PM
07/05/2007 04:38 AM,0,,07/05/2007 01:03 PM
07/05/2007 04:12 AM,0,,07/05/2007 08:56 AM
07/05/2007 04:46 AM,0,07/05/2007 09:48 AM,07/05/2007 04:36 PM
07/05/2007 05:18 AM,0,,07/05/2007 01:07 PM
07/05/2007 06:53 AM,0,,07/05/2007 01:44 PM
07/05/2007 07:12 AM,0,,07/05/2007 12:16 PM
07/05/2007 07:10 AM,0,,07/05/2007 01:45 PM
07/05/2007 07:47 AM,1,,07/05/2007 01:09 PM
07/05/2007 07:53 AM,1,,07/05/2007 12:43 PM
07/05/2007 07:59 AM,1,,07/05/2007 04:18 PM
07/05/2007 08:01 AM,1,07/05/2007 10:21 AM,07/05/2007 05:26 PM
07/05/2007 08:32 AM,1,,07/05/2007 12:08 PM
07/05/2007 09:38 AM,1,,07/05/2007 12:16 PM
07/05/2007 09:52 AM,2,,07/05/2007 07:19 PM
07/05/2007 09:56 AM,2,,07/05/2007 03:56 PM
07/05/2007 09:54 AM,2,,07/05/2007 03:13 PM
07/05/2007 10:24 AM,3,,07/05/2007 02:44 PM
07/05/2007 10:25 AM,1,,07/05/2007 04:43 PM
07/05/2007 10:38 AM,0,,07/05/2007 07:45 PM

 

[Max]

I'm not sure how you arrived at the figs in the "#waiting" col.

Anyway here's some thoughts expressed/illustrated in this sample:
http://www.savefile.com/files/991574
Waiting Line Counts.xls

2 scenarios are given:
1. With col C (Start_wait) as-is
2. With col C filled down, using Sub FillColBlanks() by Dave Peterson**

For each scenario, the formula placed in B2, copied down is:
=IF(OR(C2="",D2=""),"",SUMPRODUCT((A$2:A$100>=C2)*(A$2:A$100<=D2)*(A$2:A$100<>"")))

Hope one of the 2 scenarios brings you closer to your intents ..

**See Debra Dalgleish's:
http://www.contextures.com/xlDataEntry02.html
Excel -- Data Entry -- Fill Blank Cells

Fill Blank Cells
Fill Blank Cells Programmatically
(Sub FillColBlanks() by Dave Peterson)