Counting Items as Singles in a Multi Select Drop Down Column
Hello Smartsheets Community:
Does anyone have a simple solution to count items as individuals when using a Multi Select Drop Down Column.
In the screen shot below, we have a column where we put the potential customers we think we are going to sell a new product. What I would like to do is be able to accurately reflect total number of customers.
For example:
Amazon 2
Lowe's 2
THD 2
Lowes.com 2
THD.com 1
Any suggestions would be welcomed. I tried COUNTIFS, COUNTIF and I could not get it to work.
Best Answer
-
Try this EXACTLY (copy/paste if needed)...
=COUNTIFS({Potential Customers}, HAS(@cell, Label@row))
Answers
-
You are going to want something like this...
=COUNTIFS([Potential Customers]:[Potential Customers], HAS(@cell, "Lowe's"))
-
When I fill out the suggested formula, when I get to the step of referencing the @cell. As soon as I enter "Amazon" the @cell highlights itself and when I complete the formula, I get #UNPARASABLE. See screen shots below.
-
Use "@cell" exactly how I have it in my example formula. If you want to use a cell reference to tell it what to pull on then it would look like this...
HAS(@cell, Label@row)
-
I tried your suggestion and I am still getting the #UNPARSEABLE error. I'm sure it's user error with the way I wrote the formula. Did I write the formula correctly?
Thanks for all your help and suggestions.
Anthony D'Ambrosio
-
Paul I tried your suggestion and I am still getting the #UNPARSEABLE error. I'm sure it's user error with the way I wrote the formula :). What am I doing incorrectly?
Thanks for all your help and suggestions.
-
Try this EXACTLY (copy/paste if needed)...
=COUNTIFS({Potential Customers}, HAS(@cell, Label@row))
-
@Paul Newcome thank you very much for your help and patience while walking me through this formula. What you provide worked! One last question if you do not mind. How might I apply that same formula to count up the individual customers in a multi-select column by Project Category Type?
For example, in the screen shot below. What if we want to count up all the individual customers on our intake sheet that are assigned to ECOMM (highlighted in yellow)?
Again, I cannot thank you enough for all the help!
-
You would add another range/criteria set...
=COUNTIFS({Potential Customers}, HAS(@cell, Label@row), {Category Type}, @cell = "ECOMM")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!