vendredi 24 février 2017

Return empty instead of zero when summing range

I'm summing rows down a range using ARRAYFORMULA().

The number of rows in this range will vary over time, due to be linked to a data source that will grow. This is fine on the sheet because there's nothing else below. However, this means I can't limit the range on the array, leading to it summing empty rows at the bottom.

The complication I'm having is that where there's no data to be summed, the formula is returning 0 instead of an error or nothing. I'd like it to return nothing/empty.

Here's the formula for summing down the range by row:

=ARRAYFORMULA(SUMIF(IF(COLUMN(K2:AN),ROW(AN2:AN)),ROW(AN2:AN),K2:AN))

My typical approach to return empty is nested IF() statements, but this is incredibly bulky. There has to be a better way than:

=ARRAYFORMULA(IF(SUMIF(IF(...)...)<>0,SUMIF(IF(...)...)),"")

I've tried these lighter approaches to return empty instead, with no luck:

=ARRAYFORMULA(IFERROR(SUMIF(IF(...)...),""))

=ARRAYFORMULA(SUMIF(IF(ISNUMBER(...))...))

Aucun commentaire:

Enregistrer un commentaire