Can we make SUMIFS formulas with multiple texts? (Mixing 50/30/20 with Envelope Budget)

Google Sheets
, ,
1

Hey everyone,

I’m using both Savings Budget sheet and 50/30/20 Calculator sheet by Tiller Money.

The calculator uses =sumifs(H3:H,G3:G,“Needs”) to get data from categories sheet after we change the Groups to Needs, Wants & Freedom, which I don’t want to as I prefer an in-depth view of my expenses arranged in multiple groups, as well as for the Envelope Savings Budget.

So I tried changing the SUMIFS to =sumifs(H3:H,G3:G,“Living”) and adding multiple group texts in the forumla such as:

  • sumifs(H3:H,G3:G,“Living,Bills”)
  • sumifs(H3:H,G3:G,“Living” OR “Bills”)
  • sumifs(H3:H,G3:G,“Living”,“Bills”)

but none of this is working. So I’m confused that if we can actually do this or not? I tried reading some docs but it didn’t help.

Maybe I can just create a new row in the Categories sheet and use it specifically for the 50/30/20 calculator. How does this sound?

Let me know what do you guys think.

Cheers,
Aditya.

2

Hi @Aditya:
The trick with the SUMIFS functions is knowing that they are built for AND logic, not OR logic. So if you seek to sum amounts among multiple conditions in a formula, only the amounts that satisfy all of those conditions at the same time participate in the sum.

But it looks like you are seeking to sum amounts that may be in the groups of either Living OR Bills. In this case, you simply use and sum SUMIF statements that each search for the single condition. Like this:

=SUMIF(H3:H,“Living”,G3:G) + SUMIF(H3:H,“Bills”,G3:G)

Does that help?

1 Like
3

Hey Brad,

Thank you for your help but I ended up using a different solution. I created a column “Rule” in Categories sheet (below screenshot) and only using it with the 50/30/20 Calc.

50 30 20 Calc
50 30 20 Calc877×460 37.6 KB

50 30 20 Categories Column
50 30 20 Categories Column710×357 37.5 KB

I only issue I had with this was that I couldn’t create a range of columns far away from each other in the categories sheet, such as column A and column E. It was giving an error that “Array result was not expanded because it would overwrite data in H2.”

So I had to move the “Rule” column at B to use the =query(Categories!A:B) formula.

This is not an issue but still, Can we create a range for two columns far away from each other?

Again, thank you for your help. I really appreciate it!

Cheers,
Aditya.