Countifs formula using CONTAINS
Hi all,
I am currently working on a cross-sheet formula that is trying to determine how many projects are listed as a specific health for a specific person listed in a multi-select drop down. I have been able to determine how to get this formula to work, but only for cells that have one person listed.
=COUNTIFS({Overall Project Health}, "Yellow", {Assigned To}, "Bob Jones")
If there is another person listed in the Assigned To field with Bob, the formula will not count that project. I think I need to add CONTAINS into this formula, but am struggling with how to make it work.
Any help is appreciated.
Thanks,
Andrea
Best Answers
-
Can you post the exact formula that is giving you the error using copy/paste directly from the sheet to here?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you for the quick response, Paul! After working through this a bit more we have figured it out.
Answers
-
Hi Andrea,
I struggled with this one as well. Here's what I used and has worked for me:
=COUNTIFS([Campaign Priority]:[Campaign Priority], CONTAINS("Capital Project", @cell))
This is looking at a column named Campaign Priority which is set as a multi-select drop down.
Hope this helps!
Ally
-
Hi Ally,
I am referring to cells in a different sheet so when I reference the other sheet I am using the curly brackets. I tried your suggestion above but am still receiving and 'INVALID OPERATION' error.
=COUNTIFS({Overall Project Health}, "Green", {Assigned To}, CONTAINS("Bob Jones"))
Any other ideas?
-
You are only missing the second portion of the CONTAINS functions.
=COUNTIFS({Overall Project Health}, "Green", {Assigned To}, CONTAINS("Bob Jones", @cell))
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hi Paul - this doesn't give me an error, but I'm not getting the correct answer. What is the @cell looking at in a cross-sheet reference? Is it looking at the entire column I am referencing? Thanks!
-
The @cell reference simply tells the formula to evaluate the entire range on a cell by cell basis instead of counting the entire range as a single piece. If you are not getting the correct answer, then you are going to want to re-evaluate your data. Make sure that the entries you think should be counted actually contain "bob space jones" and not "bob space space jones" or other such discrepancies. Extra spaces are not visible when viewing a cell but will become apparent when you enter a cell to edit.
The bottom cell is a copy/paste of the top cell. It shows where the additional spaces are not shown even though they are there.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Hmm... I am still getting an error: INCORRECT ARGUMENT SET. Any other thoughts?
-
Can you post the exact formula that is giving you the error using copy/paste directly from the sheet to here?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Thank you for the quick response, Paul! After working through this a bit more we have figured it out.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 213 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!