sum a range of cells defined by VLOOKUP

[TFTAJLLYMXZP]

I have a set of sheets consisting of two areas. The top area has 4
columns of data and a fixed number of rows, though the number of rows
will vary from sheet to sheet. The first column is filled with a
simple sequence starting with 1, and other data will be added over
time.

A B C D
--------------------------------------------
1 $12.00 $ 0 $12.00
2 $11.00 $ 0 $11.00
3 $13.00 $ 0 $13.00
4 $12.50 $ 0 $12.50
5 $20.00 $ 0 $20.00
....

On the same sheet I have another area where one or more of the Column A
values are entered, followed by some columns with various aggregate
data. In the last column I would like a formula to SUM a subset of
values in the first area's Column D. The range of values must begin on
the row indicated in Column A and end 3 rows down. For example, cell X
would sum these values: $11.00, $13.00, $12.50, and $20.00. Cell Y, on
the other hand, would sum these values: $12.50 and $20.00.

A B C D
--------------------------------------------
2 X
4 Y

Does anyone have any suggestions on how to do this?

Many thanks,

Terry

 

[Max]

Assuming top area data is in cols A to D, within row2 to row10 (say)

And the bottom area table begins in row20 down,
eg in A20:A21 are the input numbers: 2, 4

Put in D20:
=SUM(OFFSET(INDIRECT("A"&MATCH(A20,$A$2:$A$10,0)+1),,3,MAX($A$2:$A$10)-A20+1))

Copy D20 down

D20 will return required results for X,
D21 returns the results for Y, and so on

Adapt to suit ..

 

[TFTAJLLYMXZP]

That's excellent, Max. Many thanks.

 

[Max]

That's excellent, Max. Many thanks.

You're welcome !
Thanks for the feedback ..