Incremental Sum but with a twist

[Bill_k]

Hello guys... need some urgent help here:

I have a number "456587"

[TABLE="width: 704"]
<tbody>[TR]
[TD="class: xl63, width: 64, align: right"]85[/TD]
[TD="class: xl63, width: 64, align: right"]458[/TD]
[TD="class: xl63, width: 64, align: right"]794[/TD]
[TD="class: xl63, width: 64, align: right"]239[/TD]
[TD="class: xl63, width: 64, align: right"]902[/TD]
[TD="class: xl63, width: 64, align: right"]395[/TD]
[TD="class: xl63, width: 64, align: right"]857[/TD]
[TD="class: xl63, width: 64, align: right"]292[/TD]
[TD="class: xl63, width: 64, align: right"]841[/TD]
[TD="class: xl63, width: 64, align: right"]516[/TD]
[TD="class: xl63, width: 64, align: right"]12[/TD]
[/TR]
</tbody>[/TABLE]


I need to write a formula in a single cell that sums 456587 with the first number, and then their resultant with the second, and then the resultant with the third so on and so forth.

some thing like =+A3+A1+A3+A1+B1+A3+A1+B1+C1... if you get what i mean.


Please help.

 

[Domenic]

Try...

=SUMPRODUCT(A3+SUBTOTAL(9,OFFSET(A1:K1,,,,COLUMN(A1:K1)-COLUMN(A1)+1)))

Hope this helps!

 

[oldbrewer]

Domenic, I for one would be really grateful if you explained how that works, thanks.

 

[Domenic]

Domenic, I for one would be really grateful if you explained how that works, thanks.

COLUMN(A1:K1)-COLUMN(A1)+1) returns the following array of values...

{1,2,3,4 ... 11}

This array of values is used as the Width argument for OFFSET and returns following array of sub-ranges...

A1
A1:B1
A1:C1
'etc
'
'
A1:K1

This array of sub-ranges is passed to the SUBTOTAL function, which sums each one and returns the array of values.

Then, each value within this array of values (the sum of each sub-range) is added to the value in A1, and an array of values is returned, which is passed to SUMPRODUCT and summed.

Hope this helps!

 

[oldbrewer]

too clever for me, but thanks.

 

[Peter_SSs]

Another option if you wanted to avoid the volatile function OFFSET, I think would be

=SUMPRODUCT(A1:K1,COLUMNS(A1:K1)-(COLUMN(A1:K1)-COLUMN(A1)))+COLUMNS(A1:K1)*A3