CONTAINS not working in SUMIFS formula.
What is wrong with this formula? =SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28), CONTAINS('"Victoria Scholly",{(Template) Channel Monthly Plan Range 6}))
All ranges are pulled in from another sheet. Main objective is to calculate the total Est. hours that are within February and are assigned to Victoria. The issue here is sometimes in the owner column two owners are assigned to the same cell which using my old formula - =SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 6}, "Victoria Scholly", {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28)) - would not pull in the hours if someone else was assigned in that column. So I figured a "contains" might work, but it is not.
(Template) Channel Monthly Plan Range 1 = Est. Hours
(Template) Channel Monthly Plan Range 4 = Due Date
(Template) Channel Monthly Plan Range 6 = Owner (Contact List)
Answers
-
Hello @Victoria Scholly
A minor adjustment to the formula above to continue the SUMIFS syntax of (sum range, range1, crit
=SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28), {(Template) Channel Monthly Plan Range 6},CONTAINS("Victoria Scholly", @cell))
(There was also an inadvertent apostrophe in front of "Victoria )
cheers
Kelly
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 430 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!