CODEWITHSUNDEEP
excelvbaexcel-formulaexcel-2007
Alex TheWebGroup
Alex TheWebGroup

Reputation: 175

How to fill cell with a running value until I meet a new number and calculate the sum?

I have a lot of rows in excel and now I found that I need to calculate the rolling sum on each row. I am probably not explaining this well, I will post a picture: enter image description here

Basically I have a lot of rows with different values across columns. I need to fill the empty cells (on each row) with the "current" value, until it meets a new number. When that happens, make the sum, overwrite the cell value with the sum, and carry on in the empty cells.

Above in the picture, first row is what I currently have, the other row is what I need.

I have absolutely 0 ideas how to go about this, please help.

Upvotes: 1

Views: 612

Answers (1)

Travis
Travis

Reputation: 353

If the row you want the value of is row 1, then the below equation should work. Paste it into column A of the row that you want to have the running total, then drag it across the row as far as you want it to calculate.

Cheers!

=sum(indirect("A1:"&SUBSTITUTE(ADDRESS(1,column(),4),1,"")&"1"))

Upvotes: 1

Related Questions