Count Function and Symbols
Hello, I want to use the dropdown symbol icon that looks like the number of people colored in blue. I want to count the number of contacts in an associated contact column and display the number of people as the icon. Can you help me determine the formula to use? If I put a =countm function in the cell it doesn't print the number as a symbol.
Best Answer
-
You will need to manually click on the dropdown arrow in any cell within the "person" column. The text listed as the options are the text strings required to be output by your formula. You would then use a nested IF to output the appropriate number of "people" based on the result of the COUNTM.
=IF(COUNTM([Contact Column Name]@row) = 0, "text for zero", IF(COUNTM([Contact Column Name]@row) = 1, "text for one", IF(..................................
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!
Answers
-
You will need to manually click on the dropdown arrow in any cell within the "person" column. The text listed as the options are the text strings required to be output by your formula. You would then use a nested IF to output the appropriate number of "people" based on the result of the COUNTM.
=IF(COUNTM([Contact Column Name]@row) = 0, "text for zero", IF(COUNTM([Contact Column Name]@row) = 1, "text for one", IF(..................................
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 @Gianni
The symbol is created using the word for the number (ie "one"), not the numerical representation of the number, ("1"). Here is a list of the values for each symbol set.
In this illustration:
- The formula in Symbol1 is =[Number]@row - that does not create a person icon
- The formula in Symbol2 is =[Text]@row - that does create a person icon
So you need to turn the count of people into the word for the number and then pass that into your symbol column. Does that make sense?
-
Thank you! That worked!
Here is the nested IF statement that I used in case anyone else wants the answer to it:
=IF(COUNTM([Alert Contacts]@row, [Assigned To]@row) = 0, "Empty", IF(COUNTM([Alert Contacts]@row, [Assigned To]@row) = 1, "One", IF(COUNTM([Alert Contacts]@row, [Assigned To]@row) = 2, "Two", IF(COUNTM([Alert Contacts]@row, [Assigned To]@row) = 3, "Three", IF(COUNTM([Alert Contacts]@row, [Assigned To]@row) = 4, "Four", IF(COUNTM([Alert Contacts]@row, [Assigned To]@row) = 5, "Five", "Five"))))))
My next part of the question is how do I count only unique contacts between both cells? The "Alert Contact" and the "Assigned to" columns have overlapping contacts occasionally.
-
You would need a helper column that is a multi-select drop down. You would then join the cells in those two columns using a line break in between. The multi-select drop down should filter out duplicates. Then you would reference just this helper column in your COUNTM.
=[First Column]@row + CHAR(10) + [Second Column]@row
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!
-
Or what Paul said. 😀
The community page used to have a pop up if someone had answered while I was slowly typing a response.
-
Ok I understand that method but it would be better without another column. Is there really no "Unique" function in smartsheets?
-
There is a DISTINCT function, but it will not evaluate the individual selections within the cell. Only that your two cells have two distinct strings in them. That's why you need the multi-select helper column. That will filter out the duplicates and provide a listing of unique selections that can then be leveraged in your COUNTM function.
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!
-
Gotit. Thanks for your help!
-
Happy to help. 👍️
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!