Find product from date range

[dlotz]

OK, a bit confused Table array on sheet 2
ColA ColB ColC ColD
# St Date end Date code#
1234 1-1-2009 5-31-2009 1111
1234 6-1-2009 12-31-2009 2222
2345 1-1-2009 5-31-2009 1111
2345 6-1-2009 12-31-2009 3333

I would like on sheet one
say
A1= #
B2= Date needing to produce Code #
C2=Code#

Please advsie

 

[Pete_UK]

Put this in C2 of Sheet2:

=SUMPRODUCT((A1=Sheet1!A2:A5)*(B2>=Sheet1!B2:B5)*(B2<=Sheet1!
C2:C5),Sheet1!D25)

Adjust the ranges to suit your real data.

Hope this helps.

Pete

 

[Jacob Skaria]

If you want to retrieve the code try the below in Sheet1 C2

=SUMPRODUCT((Sheet2!A1:A10=A1)*(Sheet2!B1:B10<=B1)*
(Sheet2!C1:C10>=B1),Sheet2!D110)

If this post helps click Yes

 

[ryguy7272]

Now I'm confused too. What do you really want to do? Transpose? Some type
of analytical thing?

I think you need to provide more info. Before and after examples are best.


Good luck,
Ryan---

 

[dlotz]

I have to be having so type of formating problem,
I get a 0 value in the cell regardless of the date and code
Table array is sorted by Emp# and the Start Date of the code
Just as shown
ColA ColB ColC ColD
This is the actual formula I typed
=SUMPRODUCT((Codes!B2:B1000=MASTER!U10)*(Codes!C2:C1000<=MASTER!Q10)*(Codes!D21000>=MASTER!Q10),Codes!E2:E1000)
MASTER! Col U is the emp # and MASTER Col Q is the Date
The function is entered into ColX
Codes!B emp# array
Codes!C Start Date array
Codes!D End Date array
Codes!E Code #
Please advise


:

 

[dlotz]

I have to be having so type of formating problem,
I get a 0 value in the cell regardless of the date and code
Table array is sorted by Emp# and the Start Date of the code
Just as shown
ColA ColB ColC ColD
This is the actual formula I typed
=SUMPRODUCT((Codes!B2:B1000=MASTER!U10)*(Codes!C2:C1000<=MASTER!Q10)*(Codes!D21000>=MASTER!Q10),Codes!E2:E1000)
MASTER! Col U is the emp # and MASTER Col Q is the Date
The function is entered into ColX
Codes!B emp# array
Codes!C Start Date array
Codes!D End Date array
Codes!E Code #
Please advise