how to count total # of time a contact appears in a column when there are multiple names in a cell

NishaTKD
NishaTKD ✭✭✭✭
edited 02/10/22 in Formulas and Functions

How to count total number of times a name (contact) appears in a column when there are multiple contacts in a cell within a column.

I have a name column where there could be multiple contacts listed within a cell. I need to count total number of time each name appeared in the column. What formula or functions would get me my desired result. Here is the screen shot to give an idea of my data and what I want to do with it (i.e. desired state).


Thanks in advance for your inputs!

Best Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    OK, so Sheet 2 is what most people call "Metrics" sheet.

    In this cell, start typing the formula I gave you.

    When you get to here,

    =COUNTIF(

    Smartsheet will popup a formula helper box. It gives you some info about the function you are using.

    Click where it says "Reference Another Sheet".

    Select Sheet A that has your data. Select the Team members column. Insert the reference. The bold section of the formula below is what yours will kind of look like, it's the reference to that column in your data sheet. Then finish the formula.

    =COUNTIF({First Sheet Range 1}, CONTAINS([Team member]@row, @cell))

    Once you see that pulls back the count for Tom, right click on the formula cell, and at the bottom of the menu, select Convert to Column Formula. It will apply the formula to all the rows in that column.

    Note: If the formula throws an error on rows WITHOUT a Team Member listed, wrap the formula in IFERROR, as such:

    =IFERROR(COUNTIF({First Sheet Range 1}, CONTAINS([Team member]@row, @cell)), "")

    This tells it to replace error messages with a blank cell.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    The problem with HAS is that while it works with multi-select Contact columns, it won't count correctly if the cell has two or more contacts in it. So if you're counting cells with contact Tom <Tom@NoEmail.com> in them, and a cell has both Tom and Lucy in it, HAS will not count that cell since it isn't an exact match.

    CONTAINS searches for characters or strings, and it works with multi-select columns, but it doesn't work with Contact columns (because Contacts are a special data type, not just plain text.)

    Luckily, there IS a workaround! If you create a helper Text/Number type column (call it "Names",) you can use that to turn your Contact column values into plain text (contact names separated by commas,) and then the CONTAINS function will work on them. Use formula:

    =[Team member]@row

    Make sure to do the same with the Team member column in your metrics sheet too.

    Now to add another criteria into it, you need to change from using COUNTIF to using COUNTIFS, along with and OR statement since you have two criteria for the Day Type:

    =COUNTIFS({First Sheet Range Names}, CONTAINS([Team Member Name]@row, @cell), {First Sheet Range Day Type}, OR(@cell = "Saturday", @cell = "Sunday"))

    ({First Sheet Range Names} is the reference you create to your new helper column on the first sheet. {First Sheet Range Day Type} is the reference you create to the Day Type column in your first sheet.)


    Lastly - formula examples sheet - it looks like they turned this into a template to download (it used to be one sheet owned by Smartsheet and shared out publicly.) You can find it in the Solutions Center, just search for formula examples, select the template and add it to your Smartsheet folders:


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    @NishaTKD the basic structure is this:

    =COUNTIF([Team member]:[Team member], CONTAINS([Team member]@row, @cell))

    However, this assumes that the list of names where you are collecting the metrics is in the same sheet and same column as your other data.

    If you're metrics are on another sheet, you would of course need to reference the Team member column in the first sheet as a range. Follow the Smartsheet prompts to Reference Another Sheet when building the formula. The end result would look something like this:

    =COUNTIF({First Sheet Range 1}, CONTAINS([Team member]@row, @cell))

    Make sense?

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • NishaTKD
    NishaTKD ✭✭✭✭

    @Jeff Reisman Thank you for your prompt response. It makes sense and I tried but isn't working the way I want. May be I am missing something. Let me elaborate a little more -

    Sheet A has the day column to show weekends day and there is another could for team members. Team member column could have multiple names if multiple people work on that day.

    On sheet 2 I would like to see list of team members and total count of weekend days they worked.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    OK, so Sheet 2 is what most people call "Metrics" sheet.

    In this cell, start typing the formula I gave you.

    When you get to here,

    =COUNTIF(

    Smartsheet will popup a formula helper box. It gives you some info about the function you are using.

    Click where it says "Reference Another Sheet".

    Select Sheet A that has your data. Select the Team members column. Insert the reference. The bold section of the formula below is what yours will kind of look like, it's the reference to that column in your data sheet. Then finish the formula.

    =COUNTIF({First Sheet Range 1}, CONTAINS([Team member]@row, @cell))

    Once you see that pulls back the count for Tom, right click on the formula cell, and at the bottom of the menu, select Convert to Column Formula. It will apply the formula to all the rows in that column.

    Note: If the formula throws an error on rows WITHOUT a Team Member listed, wrap the formula in IFERROR, as such:

    =IFERROR(COUNTIF({First Sheet Range 1}, CONTAINS([Team member]@row, @cell)), "")

    This tells it to replace error messages with a blank cell.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • NishaTKD
    NishaTKD ✭✭✭✭

    @Jeff Reisman Thanks again for your prompt response. This formula works but in my specific scenario where there are multiple contacts in a cell, it isn't working. My 'Team member' column is multiselect contact. The formula does not count the values if there are multiple name in a cell.

    Is there any other function? I tried COUNTIF with HAS and it seems to work. But I am not sure how to include more conditions - meaning only count if the day type is 'Saturday' or 'Sunday' ('Day Type' is also another column)

    Any pointers will be helpful.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    The problem with HAS is that while it works with multi-select Contact columns, it won't count correctly if the cell has two or more contacts in it. So if you're counting cells with contact Tom <Tom@NoEmail.com> in them, and a cell has both Tom and Lucy in it, HAS will not count that cell since it isn't an exact match.

    CONTAINS searches for characters or strings, and it works with multi-select columns, but it doesn't work with Contact columns (because Contacts are a special data type, not just plain text.)

    Luckily, there IS a workaround! If you create a helper Text/Number type column (call it "Names",) you can use that to turn your Contact column values into plain text (contact names separated by commas,) and then the CONTAINS function will work on them. Use formula:

    =[Team member]@row

    Make sure to do the same with the Team member column in your metrics sheet too.

    Now to add another criteria into it, you need to change from using COUNTIF to using COUNTIFS, along with and OR statement since you have two criteria for the Day Type:

    =COUNTIFS({First Sheet Range Names}, CONTAINS([Team Member Name]@row, @cell), {First Sheet Range Day Type}, OR(@cell = "Saturday", @cell = "Sunday"))

    ({First Sheet Range Names} is the reference you create to your new helper column on the first sheet. {First Sheet Range Day Type} is the reference you create to the Day Type column in your first sheet.)


    Lastly - formula examples sheet - it looks like they turned this into a template to download (it used to be one sheet owned by Smartsheet and shared out publicly.) You can find it in the Solutions Center, just search for formula examples, select the template and add it to your Smartsheet folders:


    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • NishaTKD
    NishaTKD ✭✭✭✭

    @Jeff Reisman It worked! Thank you very much for providing the solution :-)

    I am still new to Smartsheet so had to attempt few times before it worked. I cleaned up my sheet and started fresh and followed all the steps you explained in your response and it worked. Thanks again!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!