| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Max Value, Sum Product, Between Date Range | Excel Worksheet Functions | |||
| Find Last cell in Range when range is date format | Excel Discussion (Misc queries) | |||
| Find within Date Range | Excel Discussion (Misc queries) | |||
| Find the next date for a product | Excel Discussion (Misc queries) | |||
| find sum if one col = ? within specific date range | Excel Discussion (Misc queries) | |||
Hybrid Mode
