Sum columns based on fixed number

[ifejza]

I'm new in forum and I have a problem with SUM calculation.

I have a 12 column (12 month) with numbers. In one field I write one number for example 5.

If I write number 5 I want to SUM first 5 column (from A to E).

Imran,

[DonkeyOte]

You can use:

=SUM(A2:INDEX(2:2;A3))

where row 2 contains values and A3 the number (5)

or

=SUM(OFFSET(A2;;;1;A3))

the above unlike the first option is Volatile

[ifejza]

Thanks DonkeyOte for your quick answer, but I don't understand. I try to explain.

3 (A1 cell)

1 3 4 4 3 8 (this is a sum from A to C based on number 3 in cell A1)
2 3 4 5 4 9
3 3 4 4 5 10
4 3 4 5 3 11
5 3 4 4 4 12
6 3 4 5 5 13
7 3 4 4 3 14
8 3 4 5 4 15
9 3 4 4 5 16
10 3 4 5 3 17

[DonkeyOte]

Well, if we assume 3 is in A1 and your table represents columns A2:F11 with "total" in F2:F11 then:

Please Login or Register  to view this content.

(using the above, setting A1 to 0 would sum all columns)

Or

Please Login or Register  to view this content.

as before the 2nd option given it's use of Offset is Volatile (see link in sig. for more info on that)

(note in the above I have assumed your argument delimiter to be a semi colon ( rather than a comma (,))

[ifejza]

Thanks DonkeyOte,

I used Offset function and everything is OK.

Imran,