Sum product using time range

**Statsman** · 09-11-2008, 01:10 PM

Hi everyone,

I need your help!

I have a table that shows the Code, Time From and Time To columns. When a code is found, I would like a value to return when the data is between the Time From and Time To fields using the sumproduct. See below to explain myself more.

1st data
Code - A
Time From - 08:00
Time To - 08:10

2nd data
Code A
Time From - 08:30
Time To - 08:50

The result should be the following:
Time - Result
08:00 - 1
08:05 - 1
08:10 - 1
08:15 - 0
08:20 - 0
08:25 - 0
08:30 - 1
08:35 - 1
08:40 - 1
08:45 - 1
08:50 - 1
08:55 - 0

Is this douable? Your help on this is appreciated!

Statsman

**NBVC** · 09-11-2008, 01:34 PM

Try:

=SUMPRODUCT(--($A$1:$A$3<>""),--($B$1:$B$3<=A5),--($C$1:$C$3>=A5))

Where A1:C3 contains your data and A5 first time in Results table... adjust ranges to suit and copy formula down.

**manny_cb** · 09-11-2008, 01:37 PM

it should be...

change cell properties to time:
then..

this is the forumla using cells as references for restictions
A4=8:10
A5=8:15
B4=8:30
B5=8:50

b8 is where u wud type the value to be checked

=IF(OR(AND($B8>=$A$4,$B8<=$A$5),AND($B8>=$B$4,$B8<=$B$5)),1,0)

and fill down

that oughta get u going