I have to convert a simple formula in google sheets to arrayformula, but I cannot figure out how. Sample data: | P | A | B | C | D | E | | -------- | - | - | - | - | - | | string | 0 | 0 | 6 | 0 | 6 | | string | 10| 11| 16| 0 | 37| | string | 10| 3 | 6 | 0 | 19| | string | 5 | 7 | 28| 0 | 40|

Formula goes in column E. The current formula is: =IF(P2="","",SUM(A2:D2))

I am trying to do the obvious conversion like this: =ARRAYFORMULA(IF(P2:P="","",SUM((A2:A):(D2:D))))

This just gives me N/A in every cell down the sheet.

Not sure why the table above is not showing as a table. Adding screenshot for better understanding.

UPDATE: Minimal example

CodePudding user response：

Try it this way:

=ARRAYFORMULA(IF(A2:A="","",mmult(B2:E*1, transpose(B2:E2 ^ 0))))

CodePudding user response：

Assuming there's nothing below your table, the following in F2 should give you the sum per row for as many rows are present. It assumes that you will only ever be summing columns B:E.

=mmult(B2:indirect("E"&counta(A2:A) 1),sequence(counta(A2:A),1,1,0))

N.B - this isn't the only way of doing this.

EDIT Rethinking this, given the dependence on column P and the large number of rows (and again assuming that you only ever want to sum columns B:E), why not use a simple array addition?

=arrayformula(if(len(P2:P),B2:B C2:C D2:D E2:E,))

This should give a row sum for each row where the cell in column P is not empty.