Google Sheets' SUMIF/Total Array Formula - Show totals only for rows with values

0
votes

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!

1
google-sheetstransposesumifsarray-formulas

1 Answers

0
votes

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