Using SUMIFS formula with CONTAINS or HAS as Criterea
I'm trying to only add up budget line items from specific months and that do not contain the value "Total" in their account code. Here's the formula I have and just can't get right. After breaking it apart, I'm finding the CONTAINS part of the formula is what's incorrect.
=SUMIFS({Budget Detail $}, {Budget Detail Acct Code}, CONTAINS("Total", {Budget Detail Acct Code}), {Budget Detail Month}, 10)
Any help is greatly appreciated!
Answers
-
Since you already have the range established
=SUMIFS({Budget Detail $}, {Budget Detail Acct Code}, CONTAINS("Total", {Budget Detail Acct Code}), {Budget Detail Month}, 10)
The "range" in the CONTAINS function would be "@cell"
=SUMIFS({Budget Detail $}, {Budget Detail Acct Code}, CONTAINS("Total", @cell), {Budget Detail Month}, 10)
-
Thanks! This took care of it.
-
Happy to help. 👍️
-
Hi @Paul Newcome,
Can we use SUMIFS + CONTAINS + HAS? Meaning that it would sum all values in a range that contain any of the values in another cell.
Thank you,
Jonatas
-
@Jonatas Rodrigues It should be possible. Are you able to provide more details and possibly some screenshots for context?
-
@Paul Newcome Please see print below:
The result is 100, but as you can see the second code '286113' is on the first line with another code. I want the result to be 200... How can I implement CONTAINS here?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!