Sum by position

  • Thread starter Thread starter jeanulrich00
  • Start date Start date
J

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
 
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 A2:D10 (or R2C1:R10C4)
Adjust as needed.

Cheers,

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.
 
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.
Click to expand...

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
 
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
 
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
Click to expand...

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
 
<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
 
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
 
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
 
=SUM(S3:INDEX(S3:X3,L3))

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

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

- - -
Dana DeLouis
 
Back
Top