Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup based on multiple dates
In one worksheet, I have a list of payments, e.g....
Date, Amount, Description 6/1/06, $400.00 6/15/06, $300.00 7/11/06, $700.00 7/12/06, $120.00 On another worksheet I want a cashflow schedule based on the first Monday of each week, e.g... Week beginning, Cash In, Cash Out 5/29/06, $400, -$250 6/5/06, $0, -$250 6/12/06, $300, -$250 Unfortunately, the lookup formula for the Cash In column is not as simple as =VLOOKUP(A2,PaymentSchedule,2,TRUE). Two problems: (1) for the Cash In column, can I use VLOOKUP to find not just payments on that day, but the whole week (e.g. payments between 5/29/06 and 6/4/06)? (2) what if there is more than one payment in a week, can I sum them? If this is possible, I am thinking (1) will involve an array formula and (2) will use SUMPRODUCT (or array SUMIF), but I don't know how to set up the formula. Thanks for any suggestions. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup based on multiple dates
Try:
=SUMPRODUCT(--(sheet1!A1:A100=DATE(2006,5,1)),--(Sheet1!A1:A100<=DATE(2006,5,7)),--(Sheet1!B1:B100)) HTH " wrote: In one worksheet, I have a list of payments, e.g.... Date, Amount, Description 6/1/06, $400.00 6/15/06, $300.00 7/11/06, $700.00 7/12/06, $120.00 On another worksheet I want a cashflow schedule based on the first Monday of each week, e.g... Week beginning, Cash In, Cash Out 5/29/06, $400, -$250 6/5/06, $0, -$250 6/12/06, $300, -$250 Unfortunately, the lookup formula for the Cash In column is not as simple as =VLOOKUP(A2,PaymentSchedule,2,TRUE). Two problems: (1) for the Cash In column, can I use VLOOKUP to find not just payments on that day, but the whole week (e.g. payments between 5/29/06 and 6/4/06)? (2) what if there is more than one payment in a week, can I sum them? If this is possible, I am thinking (1) will involve an array formula and (2) will use SUMPRODUCT (or array SUMIF), but I don't know how to set up the formula. Thanks for any suggestions. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup across multiple worksheets | Excel Worksheet Functions | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
Multiple lookup | Excel Discussion (Misc queries) | |||
lookup multiple occurrences of a value excel | Excel Worksheet Functions | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions |