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)
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
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)
Join us at Smartsheet ENGAGE 2024 🎉
October 8  10, Seattle, WA  Register now 
It Worked!! How awesome !!! That was the piece that worked! Thank you so much !
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!