Count Function and Symbols

Gianni

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.

Paul Newcome

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(..................................

Paul Newcome

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(..................................

KPH

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?

Gianni

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.

Paul Newcome

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

KPH

Or what Paul said. 😀

The community page used to have a pop up if someone had answered while I was slowly typing a response.

Gianni

Ok I understand that method but it would be better without another column. Is there really no "Unique" function in smartsheets?

Paul Newcome

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.

Gianni

Gotit. Thanks for your help!

Paul Newcome

Happy to help. 👍️