Summation/Lookup Function

  • Thread starter Thread starter Dennis
  • Start date Start date
D

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
 
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
 
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
 
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
 
Back
Top