1

I have a row of values B2:F2 I want to SUM like i did in B3:F3 but with the use of Arrayformula.

formulas in row 3 with locked $B column:

Month Jan Feb Mar Apr May
Value 15,106 15,559 10,875 21,679 18,118
Simple Cell formula =SUM($B2:B2) =SUM($B2:C2) =SUM($B2:D2) =SUM($B2:E2) =SUM($B2:F2)

Progress: I tried this formula but it outputs the SUM of the entire range B2:F2 at once in the entire range B4:F4.

=ArrayFormula(IF(B2:F2="",,SUM(B2:$F2)))
Month Jan Feb Mar Apr May
Value 15,106 15,559 10,875 21,679 18,118
Progress =ArrayFormula(IF(B2:F2="",,SUM(B2:$F2))) 81,336 81,336 81,336 81,336

What is the best formula to get the same result in B3:F3 but using Arrayformula?
Make a copy of the example sheet.

enter image description here

Update

When tring to roll forward i discoverd the case when the value row cell are empty, like this in column J, if possible address this case in the answer

enter image description here

Osm
  • 2,699
  • 2
  • 4
  • 26
  • I think you want to add more rows to your example range for summation – Argyll Aug 15 '22 at 10:06
  • Hi @Argyll, I need the accumulated value of a single row `B2:F2` outputed using an Array based function / formula. – Osm Aug 15 '22 at 11:15
  • Hey everyone. I modified the original example sheet by adding my test and remarks. Look at [this](https://docs.google.com/spreadsheets/d/1DdC4QTuJaYzK_xf1rGATLINBXJpJKB4jTkjRRfFXzHI/edit#gid=2074816165&range=A1). – Osm Aug 15 '22 at 21:17
  • Editing [@idfurw answer](https://stackoverflow.com/a/73357317/19529694) - [Edit link](https://stackoverflow.com/posts/73357317/timeline#history_c6102ee7-43a2-4e6c-98c8-9d7dc8ed1046) From `=ArrayFormula(IF(B2:2="",,SUMIF(COLUMN(B2:2),"<="&COLUMN(B2:2),B2:2))) ` to `=ArrayFormula(IF(B1:1="",,SUMIF(COLUMN(B2:2),"<="&COLUMN(B2:2),B2:2)))` [See](https://i.imgur.com/NA5KBZ7.png) Produced the best result so far. – Osm Aug 15 '22 at 21:39
  • Conclusions for future visitors: [@idfurw answer](https://stackoverflow.com/a/73357317/19529694) is the simplist sloution using `SUMIF` but `IF(B1:1="",,` instead of `IF(B2:2="",,` , and [Player0 answer](https://stackoverflow.com/a/73361961/19529694) is the best option without `SUMIF` function, Dynamic and Computationally efficient. [See img](https://i.imgur.com/TOMHb2D.png) - and [OP tests sheet](https://docs.google.com/spreadsheets/d/1DdC4QTuJaYzK_xf1rGATLINBXJpJKB4jTkjRRfFXzHI/edit#gid=2074816165&range=A1) – Osm Aug 15 '22 at 23:08

3 Answers3

2

standard transposed running total fx will do:

=INDEX(TRANSPOSE(MMULT(TRANSPOSE((SEQUENCE(5)<=SEQUENCE(1, 5))*
 FLATTEN(B2:F2)), SEQUENCE(5, 1, 1, 0))))

enter image description here

fully dynamic and maximally lightweight:

=INDEX(IF(C2:2="",,TRANSPOSE(MMULT(TRANSPOSE((
 SEQUENCE(   MAX(COLUMN(C2:2)*(C2:2<>""))-COLUMN(C2)+1)<=
 SEQUENCE(1, MAX(COLUMN(C2:2)*(C2:2<>""))-COLUMN(C2)+1))*
 FLATTEN(INDIRECT("C2:"&ADDRESS(2, MAX(COLUMN(C2:2)*(C2:2<>"")))))), 
 SEQUENCE(   MAX(COLUMN(C2:2)*(C2:2<>""))-COLUMN(C2)+1, 1, 1, 0)))))
player0
  • 124,011
  • 12
  • 67
  • 124
  • the Good thing it refrence's the range `C2:G2` once, but limited due to hard coded ranges like `SEQUENCE(5)` when end refrence dropped produces an **Error** [See this](https://docs.google.com/spreadsheets/d/1DdC4QTuJaYzK_xf1rGATLINBXJpJKB4jTkjRRfFXzHI/edit#gid=2074816165&range=C15)... – Osm Aug 15 '22 at 21:03
  • ...but when tring to make them dynamic this is what i did so far `=INDEX(TRANSPOSE(MMULT(TRANSPOSE((SEQUENCE(MAX(COUNTA(C1:1)))<=SEQUENCE(1, MAX(COUNTA(C1:1))))* TRANSPOSE( ARRAY_CONSTRAIN(FLATTEN(C2:2),COUNTA(C1:1),1))), SEQUENCE(MAX(COUNTA(C1:1)), 1, 1, 0))))` [See this](https://docs.google.com/spreadsheets/d/1DdC4QTuJaYzK_xf1rGATLINBXJpJKB4jTkjRRfFXzHI/edit#gid=2074816165&range=H16:J16) – Osm Aug 15 '22 at 21:03
  • @Osm see: https://i.stack.imgur.com/0Qe94.png and also for examples on how to make range dynamic and lightweight: https://stackoverflow.com/questions/65435313/arrayformula-of-average-on-infinite-truly-dynamic-range-in-google-sheets – player0 Aug 15 '22 at 21:44
  • 1
    Thanks for the update, changing =INDEX(IF(C2:2="",, to =INDEX(IF(C1:1="",, produces the desired output using your approach [see this](https://i.imgur.com/2cF5JKS.png). – Osm Aug 15 '22 at 23:02
1

A simple way to calculate cumulative sum:

=ArrayFormula(IF(B1:1="",,SUMIF(COLUMN(B2:2),"<="&COLUMN(B2:2),B2:2)))
idfurw
  • 5,727
  • 2
  • 5
  • 18
  • Hi @idfurw your formula worked great, but i discoverd when rolling forward if there is an empty cell for [**example**](https://i.imgur.com/KlRiUJG.png) in column `I` the output is empty `""`, and if used in chart is interrupted, can it be done when even if some cells are empty? – Osm Aug 15 '22 at 11:37
  • Sorry, I have wrongly specified the range in the `IF` statement, it should be `B1:1` instead of `B2:2`. – idfurw Aug 18 '22 at 05:33
0
=arrayformula(mmult(if(isblank(B2:F2),0,B2:F2),if(column(B2:F2)>=transpose(column(B2:F2)),1,0)))

can produce a running sum in a row vector and can accommodate empty entries in the input range.

If you want to auto detect the number of columns in the input range, you can

  1. replace B2:F2 with array_constrain(B2:2,1,max(arrayformula(if(isblank(B2:2),,column(B2:2))))-1) and
  2. replace column(B2:F2) with array_constrain(column(B2:2),1,max(arrayformula(if(isblank(B2:2),,column(B2:2))))-1)

which is to say, cut the range leaving the number of rows that is the max column index of occupied cells in our range; minus 1 because we started with column 2.

(Also, as long as there is one arrayformula wrapping the whole formula, you can omit them in the nested inputs, as long as you preserve the () brackets.)

Nonetheless, there would be a (computational) efficiency concern.

In order to centralize the formula, in the above solution, we first created a filter for each desired entry in our running sum vector, 1,0,0,... for 1st entry, 1,1,0,... for 2nd entry, 1,1,1,0,... for 3rd, etc. And then, effectly, we apply a sum(filter(...)) via multiply by 1 or 0 using mmult. The array creation costs extra. The multiplication costs extra. And compared to iterated formulas that mutates cell by cell, we are not saving the multiply by 0 parts.

It may not end up being more than double or triple the runtime compared to iterated formulas. And you can experiment case by case. Small scale application is always fine. But for larger datasets, computational efficiency is something to keep in mind whenever we introduce extra computational steps, and potentially squaring the original amount when using mmult solutions.

Argyll
  • 8,591
  • 4
  • 25
  • 46
  • Hi @Argyll thanks for your insight, I tested `=mmult(sequence(1,rows(B2:2),1,0),B2:2)` Throws an error: [**Error**](https://i.imgur.com/cDy6nOy.png) Function MMULT parameter 2 expects number values. But '' is a empty and cannot be coerced to a number, but your Approach led me to a similar [answer](https://stackoverflow.com/a/44360616/19529694) – Osm Aug 15 '22 at 11:32
  • @Osm: Please see edits. – Argyll Aug 15 '22 at 14:54
  • @Osm: I did write an `mmult` answer because originally I thought you wanted sums of columns. For simple running sums, it's best to find alternatives that are more computational efficient. And perhaps the fastest is cell-by-cell formulas. – Argyll Aug 15 '22 at 15:22
  • Yes, of course, I saw all the [edits](https://stackoverflow.com/posts/73359664/timeline). Computational efficiency in the case of a financial statement isn't a big deal. Using the `mmult` function is a good different approach to consider, thanks. – Osm Aug 15 '22 at 21:27