SUMIFS & CONTAINS using multiple columns and criteria.
Hello All!
I am trying to sum cell numbers if conditions (per row) meet the acceptance criteria in two other columns. One of the criteria columns contains multiple contacts. I have tried using MS Copilot AI and even its answers do not work. The formula I am using is: "=SUMIFS([Task Ratio]:[Task Ratio], NeedsText:NeedsText, "Quick Win", CONTAINS("First.Last2", AssignedText:AssignedText))" The attachment contains an excel file for reference. I have also tried using HAS instead of CONTAINS (Formula: "=SUMIFS([Task Ratio]:[Task Ratio], NeedsText:NeedsText, "Quick Win", HAS(@cell, "First.Last2", AssignedText:AssignedText, @row))")
Any help is appreciated especally since the AI was not able to rectify this.
Regards
CCR
Best Answer
-
This should fix the issue for you:
=SUMIFS([Task Ratio]:[Task Ratio], NeedsText:NeedsText, CONTAINS("Quick Win", @cell), AssignedText:AssignedText, CONTAINS("First.Last2", @cell))
Based on the criteria inside of the excel sheet, there aren't any rows being summed though -- not sure if this is intentional.
AI can't beat us yet 😉
Sincerely,
Jacob Stey
Answers
-
This should fix the issue for you:
=SUMIFS([Task Ratio]:[Task Ratio], NeedsText:NeedsText, CONTAINS("Quick Win", @cell), AssignedText:AssignedText, CONTAINS("First.Last2", @cell))
Based on the criteria inside of the excel sheet, there aren't any rows being summed though -- not sure if this is intentional.
AI can't beat us yet 😉
Sincerely,
Jacob Stey
-
Jacob;
Works very well, and it properly adds up the Task Ratio for individual resources.
Thank you!
CCR
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 429 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!