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({FY2023-2024 Working Budget Phase I SP Range 1}, CONTAINS({FY2023-2024 Working Budget Phase I SP Range 3}, "R", {FY2023-2024 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)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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({FY2023-2024 Working Budget Phase I SP Range 1}, CONTAINS(@cell, "R"), {FY2023-2024 Working Budget Phase I SP Range 4})
Let me know if that works!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!