Sum by position

[jeanulrich00]

Hi

I hava an excel sheet with 61 columns of number a,b,c,d ......
starting at row 3 and finishing at row 30

What I want to do with a formula is this.

Is it possible to have a reference cell let say A1. in this celle a
put a number let say 3

For each row I need to put a formula.

The formula should do that: start at the beginning of the column and
make the sum for next 3 columns to the right

So the formula will make the sum of the 3 first column (A+B+C)

If I put 5 in the celle reference (A1) the formula will add 5 columns
(A+B+C+D+E)

If I put 8 the formula will make the sum of the first 8 columns

Thanks

 

[Joerg Mochikun]

Put the number of columns you want to sum into A1 and following formula into
A2:
=SUM(INDIRECT("R2C1:R10C"&A1,FALSE))

The formula assumes that your data are in A210 (or R2C1:R10C4)
Adjust as needed.

Cheers,

Joerg Mochikun

 

[Joerg Mochikun]

Sorry, that was too quick: Don't put formula into A2, since in the example
that would be part of the data area. Choose e.g. B1.

 

[jeanulrich00]

Sorry, that was too quick: Don't put formula into A2, since in the example
that would be part of the data area. Choose e.g. B1.

the formula proposed by Joerg does not work


hers an example of what I need


cell L3 is the reference

what I have

S T U V W X
10 5 22 11 23 9 (formula) if L3 = 2 Answer would be 15
If L3 = 4 Answer would be 37 If L3 = 5 Answer would be 71

I have try =SUM(INDIRECT("R10C19:R10C25"&L3,FALSE)) R10 is row
10 and it gives me #Ref!

Thanks for helping

 

[Pete_UK]

That is not what Joerg gave you. Try this:

=SUM(INDIRECT("R10C19:R10C"&(18+L3),FALSE))

I'm not sure why he had FALSE in there, though.

Hope this helps.

Pete

 

[jeanulrich00]

That is not what Joerg gave you. Try this:

=SUM(INDIRECT("R10C19:R10C"&(18+L3),FALSE))

I'm not sure why he had FALSE in there, though.

Hope this helps.

Pete

Hi thanks for the formula

The only 2 problem is that I cannot add column BEFORE the starting of
the first column to count (in that case column 18)
and I cannot copy the formula to another row, I have to correct it
manualy

thanks again

 

[Niek Otten]

<I'm not sure why he had FALSE in there, though.>

To make INDIRECT use R1C1 reference style

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

That is not what Joerg gave you. Try this:

=SUM(INDIRECT("R10C19:R10C"&(18+L3),FALSE))

I'm not sure why he had FALSE in there, though.

Hope this helps.

Pete

 

[Pete_UK]

Thanks, Niek. I've never used the two together.

Pete

 

[Bernd P]

Hello,

Hmm, why should we use INDIRECT? We would force Excel to behave like
Sisyphus and to calculate that formula over and over again whenever F9
is hit.

A non-volatile solution would be:

=SUM(S3:INDEX(S3:X3,L3))

Regards,
Bernd

 

[Joerg Mochikun]

Bernd, that's definitely more elegant (and copyable, if L3 is nailed with
$L$3). I wasn't aware that the OP needed formulas for each row.
Joerg

 

[Dana DeLouis]

=SUM(S3:INDEX(S3:X3,L3))

One other way that Excel's help seems to suggest...

=SUM(OFFSET(S3,,,1,L3))

- - -
Dana DeLouis