# 2 Data Set contains time

#### DoomBlasZt

##### New Member
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

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### 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:
Code:
``=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

##### New Member
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.

Replies
7
Views
83
Replies
9
Views
142
Replies
3
Views
36
Replies
3
Views
230
Replies
3
Views
37