2 Data Set contains time

[DoomBlasZt]

I have 2 data sets :

1. Data order
[TABLE="width: 231"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Product[/TD]
[TD]Order Time[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]1:30 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]2:00 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]2:15 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]2:15 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]2:15 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]2:15 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]2:30 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]2:30 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]2:30 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]2:45 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]3:00 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD="align: right"]3:15 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD="align: right"]3:30 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD="align: right"]3:45 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD="align: right"]3:45 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD="align: right"]4:00 AM[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

2. Data schedule
[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Product[/TD]
[TD="class: xl65, width: 64"]Start[/TD]
[TD="class: xl65, width: 64"]End[/TD]
[TD="class: xl65, width: 64"]Duration[/TD]
[/TR]
[TR]
[TD="class: xl66"]AAA[/TD]
[TD="class: xl68"]2:00[/TD]
[TD="class: xl68"]2:45[/TD]
[TD="class: xl69"]45[/TD]
[/TR]
[TR]
[TD="class: xl67"]BBB[/TD]
[TD="class: xl68"]3:00[/TD]
[TD="class: xl68"]4:00[/TD]
[TD="class: xl69"]60[/TD]
[/TR]
</tbody>[/TABLE]


Is there any formula to find how many AAA product was ordered on time schedule? The answer should be 9

Thanks

 

[Joe4]

Yes. Let's say that your Data Order data (not counting the title row) is in range A2:C19.
And let's say that the Data Schedule data (not counting the title row) is in range E2:H3.

Then enter this formula in I2 to get your count:

=SUMPRODUCT(--(A$2:A$19=E2),--(B$2:B$19>=F2),--(B$2:B$19<=G2),--(C$2:C$19))

If you copy it down to I3, you will get the count for BBB in the 3:00 - 4:00 range.

 

[DoomBlasZt]

Thanks, it works

Never use sumproduct before and confuse for double negative (--)

But i will google and learn it.

Thanks again for your help.. GBU

 

[Joe4]

You are welcome.

Never use sumproduct before and confuse for double negative (--)

Each of those expressions returns TRUE/FALSE. -- simply coerces TRUE/FALSE to the values 1/0 so we can use them in multiplication.