How to sum X number of cells

[zeegerman]

Hi everyone,

I would really appreciate your help with the following as I have been stuck on this for a little while. Here is an overview of the layout:

1. Cells A1 and B1 contain the titles of the columns A and B.

2. In column A, the cells A2:A21 each contain a number ranging from 1 to 20. No two cells have the same number.

3. In column B, the cells B2:B21 each have a different number value. There are no blank cells or cells containing letters.


What I would like to have is as follows:

1. A function set beneath column A that can automatically locate the number 15 in column A (in this example, this is cell A16), and then sum the values in the previous 5 cells of column B (in this case, it is SUM(B12:B16)).

2. The sum value should then appear at the bottom of column B.

I have attached a worksheet with the layout described above to make your lives easier. I look forward to your response.

Many thanks!

[XOR LX]

Hi.

Slightly offbeat approach, but non-volatile, so perhaps even preferable to a construction using INDEX:INDEX (and certainly preferable to ones using OFFSET or INDIRECT):

=SUM(INDEX(B2:B21,N(IF(1,MATCH(15,A2:A21,0)-{0,1,2,3,4}))))

Regards

[oeldere]

See the attached file.

The result is in the green cell.

[zeegerman]

Hi.

Slightly offbeat approach, but non-volatile, so perhaps even preferable to a construction using INDEX:INDEX (and certainly preferable to ones using OFFSET or INDIRECT):

=SUM(INDEX(B2:B21,N(IF(1,MATCH(15,A2:A21,0)-{0,1,2,3,4}))))

Regards

Hi XOR LX,

That's magnificent, and just what I was looking for. Many thanks for your help!

[zeegerman]

Hi oeldere,

Thanks very much for your quick response, it is a big help.

All the best!

[XOR LX]

You're welcome!