CountIF with multiple values in the same cell (same sheet)
Hello,
I'm using a CountIf formula in Sheets Summary to count how many times a project manager (using the contact list for my column) is assigned in this year's strategic initiatives. I was able to get it to work with one person per cell, however, I can't figure it out when their's multiple people per cell.
Current formula: =COUNTIF([Project Manager]:[Project Manager], "Name")😀
Best Answer
-
The CONTAINS function would require a "range".
=countif([Project Manager]:[Project Manager], contains("name", @cell))
The problem with this though is that the CONTAINS function does not like contact columns. You would have to switch over to either a FIND or a HAS function.
=COUNTIFS([Project Manager]:[Project Manager], HAS(@cell, "Name"))
Answers
-
Maybe try: =countif([Project Manager]:[Project Manager], contains("name"))
Holly Conrad Smith
Director of Technology & Innovation 💡 at Streamline
CliftonStrengths Top 5: Deliberative, Restorative, Achiever, Consistency, Harmony
-
@hollyconradsmith I'm getting an "INCORRECT ARGUMENT SET" error
-
The CONTAINS function would require a "range".
=countif([Project Manager]:[Project Manager], contains("name", @cell))
The problem with this though is that the CONTAINS function does not like contact columns. You would have to switch over to either a FIND or a HAS function.
=COUNTIFS([Project Manager]:[Project Manager], HAS(@cell, "Name"))
-
@Paul Newcome That worked! Thank you.
-
Happy to help. 👍️
-
@Paul Newcome You have some great ideas. I am stuck on mine as well and would love any help you can offer! I am compiling survey results and we are assigning a ranking category for each comment. The issue is, some comments are long and have multiple categories. When I go to total the ranking categories per location and the grand total for all locations, it's not pulling the cells with multiple comments.
I read through your comments where you helped others. When you put @cell in your formula, is that the range of cells? I am newer to smartsheet and need some practice and help!!
Example below:
-
@KC3001 When I use "@cell" in a formula, it should be entered exactly as is. Basically it tells the function to evaluate the previously established range on a cell by cell basis.
=COUNTIFS([Project Manager]:[Project Manager], HAS(@cell, "Name"))
This basically says to evaluate the [Project Manager] column and count where each individual cell has "Name".
=COUNTIFS([Project Manager]:[Project Manager], HAS(individual cell, "Name"))
-
Ok, thank you for your help. I think I'm still confused since it is giving me an error. I'll keep trying!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 143 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 298 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!