Find product from date range
 

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

Find product from date range
 

Put this in C2 of Sheet2:

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

Adjust the ranges to suit your real data.

Hope this helps.

Pete

On Oct 16, 4:22*pm,
wrote:
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

Find product from date range
 

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!D1:D10)

If this post helps click Yes
---------------
Jacob Skaria


" wrote:

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

Find product from date range
 

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---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


" wrote:

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

Find product from date range
 

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

This is the actual formula I typed
=SUMPRODUCT((Codes!B2:B1000=MASTER!U10)*(Codes!C2: C1000<=MASTER!Q10)*(Codes!D2:D1000=MASTER!Q10),Co des!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


"Jacob Skaria" wrote:


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!D1:D10)

If this post helps click Yes
---------------
Jacob Skaria


" wrote:

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

Find product from date range
 

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

This is the actual formula I typed
=SUMPRODUCT((Codes!B2:B1000=MASTER!U10)*(Codes!C2: C1000<=MASTER!Q10)*(Codes!D2:D1000=MASTER!Q10),Co des!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




"ryguy7272" wrote:

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---
--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


" wrote:

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