2 Data Set contains time

[DoomBlasZt]

I have 2 data sets :

1. Data order

Product	Order Time	Qty
AAA	1:30 AM	1
AAA	2:00 AM	1
AAA	2:15 AM	1
AAA	2:15 AM	1
AAA	2:15 AM	1
AAA	2:15 AM	1
AAA	2:30 AM	1
AAA	2:30 AM	1
AAA	2:30 AM	1
AAA	2:45 AM	1
AAA	3:00 AM	1
AAA	3:00 AM	1
AAA	3:00 AM	1
BBB	3:15 AM	1
BBB	3:30 AM	1
BBB	3:45 AM	1
BBB	3:45 AM	1
BBB	4:00 AM	1

<colgroup><col><col><col></colgroup><tbody>
</tbody>

2. Data schedule

Product	Start	End	Duration
AAA	2:00	2:45	45
BBB	3:00	4:00	60

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

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.