Need a SUMIF formula Contains or HAS for Category fund to total Budget in referenced sheet.
I need a SUMIF formula that will sum all Cat's in the identified operating budget. I thought I could use the CONTAINS or HAS but its not working. The category (not purple Budget Category) will have a number after but I don't need that number. The code in Cat is the letter (R, TO, E, TI) I need so it sums the revised operating budget. Below is what I started but it doesn't work.
=SUMIF({FY20232024 Working Budget Phase I SP Range 1}, CONTAINS({FY20232024 Working Budget Phase I SP Range 3}, "R", {FY20232024 Working Budget Phase I SP Range 4}))
Best Answer

Hi @Linda F
My apologies! I used the wrong syntax for CONTAINS... I'm so used to working with HAS, which uses @cell first.
Try this:
=SUMIFS({Budget Column}, {CAT Column}, CONTAINS("R", @cell), {Account/SA # Column}, [Account/SA]@row)
Answers

Hey @Linda F
CONTAINS looks like the right way to go!
This is the structure I would suggest:
=SUMIF({CAT Column}, CONTAINS(@cell, "R"), {Budget Column})
So if I'm understanding your ranges correctly:
=SUMIF({FY20232024 Working Budget Phase I SP Range 1}, CONTAINS(@cell, "R"), {FY20232024 Working Budget Phase I SP Range 4})
Let me know if that works!
Cheers,
Genevieve

I think it would work but I must leaving out one more thing, the Account/SA #. So there are 2 criteria to lookup. One is the account/SA # (on 1 sheet) and the other is the Budget Category (reference sheet  second sheet). Once we have that then Sum. The formula you gave me works but I also need to consider the Acct/SA we are summing for.

Hi @Linda F
Thanks for clarifying! If you have two ranges and criteria then you'll want to use SUMIFS with an S. The structure for this then has the column to SUM at the front of the formula, like so:
=SUMIFS({Budget Column}, {CAT Column}, CONTAINS(@cell, "R"), {Account/SA # Column}, [Account/SA]@row)
See: SUMIFS Function
Cheers,
Genevieve

So the formula works but it doesn't pull in cat's that contain the letter "R", "E", "TO", "TI". BUT if I put in the correct categories such as R27 then it pulls it in  instead of contains  could there be another function that is not so specific to the cat number in the cat code?

Hi @Linda F
My apologies! I used the wrong syntax for CONTAINS... I'm so used to working with HAS, which uses @cell first.
Try this:
=SUMIFS({Budget Column}, {CAT Column}, CONTAINS("R", @cell), {Account/SA # Column}, [Account/SA]@row)

It Worked!! How awesome !!! That was the piece that worked! Thank you so much !
Help Article Resources
Categories
Check out the Formula Handbook template!