Calculating a sum from a given number of rows

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

tunupo - 12 May 2008 04:37 GMT

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

Reply to this Message

JE McGimpsey - 12 May 2008 05:38 GMT

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

> Hello,
>
[quoted text clipped - 18 lines]
>
> Thanks!!

Reply to this Message

T. Valko - 12 May 2008 05:57 GMT

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.

Signature

Biff
Microsoft Excel MVP

> Hello,
>
[quoted text clipped - 18 lines]
>
> Thanks!!

Reply to this Message

tunupo - 12 May 2008 06:30 GMT

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!

Reply to this Message

Rate this thread: