Summation/Lookup Function

[Dennis]

I have two worksheets.

On the first one, I have two dates
August 1
August 5

On the second one, I have a f/x rate that corresponds to
each date.

August 1 1.33
August 2 1.34
August 3 1.37
August 4 1.32
August 5 1.66

I'm trying to figure out how I make Excel sum up the f/x
rate between the two dates on the first worksheet by
looking at the second worksheet.

Any ideas?

Thanks

 

[Alan Beban]

With the two dates in D1 and D2 and the table of rates in A1:A5 of
Sheet4, then enter in a cell on the same sheet as the two dates

=SUM(INDIRECT("Sheet4!"&"B"&MATCH(D1,A1:A5,0)&":"&"B"&MATCH(D2,A1:A5,0)))

Alan Beban

 

[Alan Beban]

Whoops! Erase, erase. It seems that INDIRECT won't accept the reference
to a different sheet the way I tried to do it.

Alan Beban

 

[Biff]

Hi Dennis,

Here's one way:

With the first sheet dates in cells A1,A2. The second
sheet dates in col A1:A5 and the f/X numbers in col B:

=SUMPRODUCT((Sheet2!A1:A5>=Sheet1!A1)*(Sheet2!
A1:A5<=Sheet1!A2)*Sheet2!B1:B5)

Biff