countIF a value column if the attribute is along side another attribute in a "Multi select Column"
Does anybody know if you can create a formula that will count the values in a “dropdown (Multi-Select) column", when adding summary fields on a Smartsheet list?
What is happening now is, if I have Apple and Orange in one field and I want a count for Apple. it will not count it. Only if I have Apple along in a field?
If I am counting how many time the word apple is in a column I want it to count it if it is alone or if it is along side another selection in that some cell.
Best Answer
-
You can use the CONTAINS function in your formula to get a count. Example:
=COUNTIFS([Column1]:[Column1], CONTAINS("Apple", @cell))
That would give you the total number of cells that have Apple in it. If you want a distinct count for when Apple is alone use this:
=COUNTIFS([Column1]:[Column1], "Apple")
If you want a count of Apple + something else, this would work but it is cumbersome if you have a lot of potential pairings:
=COUNTIFS([Column1]:[Column1], AND(CONTAINS("Apple", @cell), CONTAINS("Orange", @cell))
Answers
-
You can use the CONTAINS function in your formula to get a count. Example:
=COUNTIFS([Column1]:[Column1], CONTAINS("Apple", @cell))
That would give you the total number of cells that have Apple in it. If you want a distinct count for when Apple is alone use this:
=COUNTIFS([Column1]:[Column1], "Apple")
If you want a count of Apple + something else, this would work but it is cumbersome if you have a lot of potential pairings:
=COUNTIFS([Column1]:[Column1], AND(CONTAINS("Apple", @cell), CONTAINS("Orange", @cell))
-
Thank you, that did the trick! :-)
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!