Can you add in criteria that uses contains in a SUMIFS formula?
Right now here is my current formula: =SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 6}, "Victoria", {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28)). Works great, however, I have the formula so it is pulling in the hours associated with "Victoria". Victoria is listed in the "owner column", which sometimes a cell in the owner column has more than one person in it, which then does not pull in the hours. Is there a way to have the formula pull in the hours even if there is another owner in the cell?
Best Answer
-
Try this
=SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 6}, CONTAINS("Victoria", @cell), {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28))
What I didn't ask in your other post was if the column you were searching was a contact column, or just a name in a cell. CONTAINS does not work in a contact column, but FIND does. FIND however, returns a number, not true or false. We must then look for a value >0 to determine if something was actually found.
=SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 6}, FIND("Victoria", @cell)>0, {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28))
cheers
Kelly
Answers
-
Try this
=SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 6}, CONTAINS("Victoria", @cell), {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28))
What I didn't ask in your other post was if the column you were searching was a contact column, or just a name in a cell. CONTAINS does not work in a contact column, but FIND does. FIND however, returns a number, not true or false. We must then look for a value >0 to determine if something was actually found.
=SUMIFS({(Template) Channel Monthly Plan Range 1}, {(Template) Channel Monthly Plan Range 1}, >0, {(Template) Channel Monthly Plan Range 6}, FIND("Victoria", @cell)>0, {(Template) Channel Monthly Plan Range 4}, >=DATE(2021, 2, 1), {(Template) Channel Monthly Plan Range 4}, <=DATE(2021, 2, 28))
cheers
Kelly
-
This worked! Thank you SO much for your help on this!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!