Hello,

I'm looking for a formula that can calculate the sum of a number of
rows given. In the example below the formula should output the sum of
the figures from the last 7 rows (starting from the right to the
left).

---------------------------------------------
Rows (from right to left) A1: 7

A B C D E F G H I J K
4 5 3 2 8 9 5 6 3 2 4

Output: sum rows E-K
---------------------------------------------

I used to calculate this using a IF formula, however that only worked
until i reached the maximum of 6 IF brackets "()".

Does anybody has an idea?

Thanks!!

One way:

=SUM(OFFSET(E1,0,0,1,A1)

Note: The standard term for the group of cells going down under "A" is a
column. The standard interpretation of 'row' is cells going across.

In article
,
tunupo wrote:

Hello,

I'm looking for a formula that can calculate the sum of a number of
rows given. In the example below the formula should output the sum of
the figures from the last 7 rows (starting from the right to the
left).

---------------------------------------------
Rows (from right to left) A1: 7

A B C D E F G H I J K
4 5 3 2 8 9 5 6 3 2 4

Output: sum rows E-K
---------------------------------------------

I used to calculate this using a IF formula, however that only worked
until i reached the maximum of 6 IF brackets "()".

Does anybody has an idea?

Thanks!!

It depends!

You want the sum of the last 7 entries from right to left. What if there
aren't 7 entries? Might there be any empty/blank cells in-between? Like
this:

A B C D E F G H I J K
4 5 3 2 8 9 6 3 4

In that case, do you want the sum of the last 7 entries or the last 7 cells.
If you want the last 7 *entries* the result would be 35. If you want the sum
of the last 7 *cells* the result would be 30.


--
Biff
Microsoft Excel MVP


"tunupo" wrote in message
...
Hello,

I'm looking for a formula that can calculate the sum of a number of
rows given. In the example below the formula should output the sum of
the figures from the last 7 rows (starting from the right to the
left).

---------------------------------------------
Rows (from right to left) A1: 7

A B C D E F G H I J K
4 5 3 2 8 9 5 6 3 2 4

Output: sum rows E-K
---------------------------------------------

I used to calculate this using a IF formula, however that only worked
until i reached the maximum of 6 IF brackets "()".

Does anybody has an idea?

Thanks!!

Thanks for your helpful replies. I could work it out as follows:

=Sum(Offset(K1,0,0,1,-A1))

Whereas K1 stands for the last column with numbers and A1 stands for
the input of number of cells to calculate back.

Many thanks!