Calculating a sum from a given number of rows

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!!

JE
Sun May 11 23:38:39 CDT 2008

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
<622799fb-9cec-42a8-994d-207eb4148dd6@j22g2000hsf.googlegroups.com>,
tunupo <ivo.ruff@gmail.com> 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!!

T
Sun May 11 23:57:27 CDT 2008

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" <ivo.ruff@gmail.com> wrote in message
news:622799fb-9cec-42a8-994d-207eb4148dd6@j22g2000hsf.googlegroups.com...
> 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!!

tunupo
Mon May 12 00:30:36 CDT 2008

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!