My colleague and I are using the following array formulas to automatically add up five individual values received in a Google Sheet from a Google Form:
=ArrayFormula(SUMIF(IF(COLUMN(G2:K2),ROW(G2:K200)),ROW(G2:K503),G2:200))
=ArrayFormula({"TOTAL";mmult(G2:K,sign(transpose(column(G2:K))))})
We were wondering if there is any way to tweak these formulas so that a number appears in the 'Total' column (in this case column L) only if values appear on that row. In short, we want to avoid a long column of 0s in the Total column in rows which haven't received any values/inputs from the Google Sheet yet.
Any suggestions would be really welcome!
you can add IF and check column A (in this example) and if empty then array formula will display nothing
=ARRAYFORMULA(IF(LEN(A1:A), SUMIF(IF(COLUMN(G2:K2), ROW(G2:K200)), ROW(G2:K503), G2:200), ))
=ARRAYFORMULA(IF(LEN(A2:A), {"TOTAL"; MMULT(G2:K, SIGN(TRANSPOSE(COLUMN(G2:K))))}, ))
tweak A1:A based on in which row formula sits and based on what column is not empty