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.

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.