2 Data Set contains time

DoomBlasZt

New Member
Joined
May 5, 2017
Messages
9
I have 2 data sets :

1. Data order
ProductOrder TimeQty
AAA1:30 AM1
AAA2:00 AM1
AAA2:15 AM1
AAA2:15 AM1
AAA2:15 AM1
AAA2:15 AM1
AAA2:30 AM1
AAA2:30 AM1
AAA2:30 AM1
AAA2:45 AM1
AAA3:00 AM1
AAA3:00 AM1
AAA3:00 AM1
BBB3:15 AM1
BBB3:30 AM1
BBB3:45 AM1
BBB3:45 AM1
BBB4:00 AM1

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

2. Data schedule
ProductStartEndDuration
AAA2:002:4545
BBB3:004:0060

<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
 

Some videos you may like

Excel Facts

Links? Where??
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 5, 2017
Messages
9
Thanks, it works

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

But i will google and learn it.

Thanks again for your help.. GBU :biggrin:
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,456
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,004
Messages
5,526,228
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top