Attempting to modify a formula, SUMIFS letting me down

Google Sheets
1

Hi all,

I’ve decided to modify the monthly analysis template to show hidden expenses, but keep them out of totals. This allows me to budget individual credit cards: $1k on this one, $2k on that one as big blocks. But then also budget purchases: $500 on amazon, $500 on groceries. Then the credit card spending is excluded from totals to avoid double-counting it.

I went into Monthly Analysis and opened hidden cells. I added an extra column to category lookup in that sheet, so hidden categories show, but a “Hide” column is there. All was well. Now to exclude from totals… I got stuck on a technical issue.

The formula for totals is in cell BC3. It is also copied to the right for all the months. This is what it says:

ARRAYFORMULA(IF(ISTEXT($BA3:$BA),
     SUMIF($AI$7:$AI&$AJ$7:$AJ,$BA3:$BA&$BB$3:$BB,AN7:AN),
     IFERROR(1/0)))

I have to replace SUMIF with SUMIFS in order to apply a second criterion. SUMIFS should then look like this:

SUMIFS ( AN7:AN, //range goes first
   $AI$7:$AI&$AJ$7:$AJ,$BA3:$BA&$BB$3:$BB, //first criterion unchanged
   $AL$7:$AL, "<>Hide")  // my new criterion range/condition

This was not working - the entire column array wound up with identical values

So to take a smaller step, I only changed SUMIF into SUMIFS without adding the second criterion.

SUMIFS ( AN7:AN, //range goes first
   $AI$7:$AI&$AJ$7:$AJ,$BA3:$BA&$BB$3:$BB
)

And discovered that, sure enough, simply rearranging the formula caused the column to have the same number as the top. Can anyone help with some tech expertise?

Thank you!
Kate

2

I found out why I was having a problem. Google Sheets ARRAYFORMULA With Examples | Coupler.io Blog explains that SUMIFS does not expand. There are work-arounds, like using multiple ranges with &.

The answer I was looking for was this:

SUMIF($AI$7:$AI&$AJ$7&**$AL7:AL**:$AJ,$BA3:$BA&$BB$3&$**CELL**:$BB,AN7:AN),

WHERE CELL is any blank cell in order to ask that “Hide” is blank.

In the end, after I got everything working, I came to decide that a more elegant solution is to copy the Monthly Analysis and create a “hidden budgets” report, which flips Hide logic, and only shows categories that are hidden. Much simpler and no new formulas.

3

Nice work getting to the bottom of that, @Kate. There are a handful of functions that don’t play well with ARRAYFORMULA(). Sadly, they don’t give much feedback when they fail… they… are… just… frustrating.

As the footprint of this community’s templates expand, there will be more and more prior art to reference for how to use formulas to solve complex data problems. It’s a great reference already.