Count Function and Symbols

Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    KPH ✭✭✭✭✭✭
    Options

    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
    Gianni ✭✭
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    KPH ✭✭✭✭✭✭
    edited 11/17/23
    Options

    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
    Gianni ✭✭
    Options

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Gianni ✭✭
    Options

    Gotit. Thanks for your help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!