How to count contacts or email address in contact column

jmo
jmo ✭✭✭✭✭✭

Hey folks - I created a column that is a contact list. In that column there are full email addresses or the Smartsheet contact:


Question - how do I create a summary formula that counts either of these and gives me a full count for the number of times either Jeff Moser or jeff.moser@email.com are used?

Best Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓

    Are you looking for a cross sheet reference, if so:

    =SUM((COUNTIFS({LookupUser Range 1}, "Jeff Moser")), (COUNTIFS({LookupUser Range 1}, "jeff.moser@aol.com")))

    where {LookupUser Range 1} is your "email" column with this data:



  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @jmo

    What is your column name? It looks like you're referencing two columns here. You'll need to ensure that your columns are referenced letter-for-letter the exact same as what's in your sheet.

    It also looks like your final criteria is missing an end quote:

    has(@cell, "jeff.moser@usaa.com) should be has(@cell, "jeff.moser@usaa.com")


    Try this:

    =SUM(COUNTIFS([Task transitioned to]:[Task transitioned to], HAS(@cell, "jeff moser")), COUNTIFS([Task transitioned to]:[Task transitioned to], HAS(@cell, "jeff.moser@usaa.com")))


    If this still doesn't work, can you post a screen capture of your sheet, but block out sensitive data? A screen capture with the formula open to show if the colours are highlighting would be useful as well.

    Thanks!

    Genevieve

Answers

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓

    Are you looking for a cross sheet reference, if so:

    =SUM((COUNTIFS({LookupUser Range 1}, "Jeff Moser")), (COUNTIFS({LookupUser Range 1}, "jeff.moser@aol.com")))

    where {LookupUser Range 1} is your "email" column with this data:



  • jmo
    jmo ✭✭✭✭✭✭

    @James Keuning - that worked as a summary sheet formula but am having a bit of trouble getting it to work on a "summary sheet" I created.

    I'll keep at it tho.

    Thanks!

  • jmo
    jmo ✭✭✭✭✭✭

    Still having a problem with the sheet summary formula NOT counting all full email addresses or contact name (ie Jeff Moser). This column allows for multiple email addresses but it doesn't look like it counts my name if someone else's email/contact info is also in that cell.

    Any ideas how to rectify this?

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @jmo

    Try using the HAS function (see here) as well, which will look to see if a cell has this value (instead of equals this value exactly).

    =SUM((COUNTIFS({LookupUser Range 1}, HAS(@cell, "Jeff Moser"))), (COUNTIFS({LookupUser Range 1}, HAS(@cell, "jeff.moser@aol.com"))))

    Let me know if this works for you!

    Cheers,

    Genevieve

  • jmo
    jmo ✭✭✭✭✭✭

    @Genevieve - I tried incorporating HAS and get an UNPARSEABLE:

    =SUM((COUNTIFS([Task transitioned to]:[Task transitioned to (EMG Owner)], has(@cell, "jeff moser"))), (countifs([Task transitioned to]:[Task transitioned to (EMG Owner)], has(@cell, "jeff.moser@usaa.com))))

    Also - I'm trying to put this as a sheet summary formula, if that makes a difference?

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer ✓

    Hi @jmo

    What is your column name? It looks like you're referencing two columns here. You'll need to ensure that your columns are referenced letter-for-letter the exact same as what's in your sheet.

    It also looks like your final criteria is missing an end quote:

    has(@cell, "jeff.moser@usaa.com) should be has(@cell, "jeff.moser@usaa.com")


    Try this:

    =SUM(COUNTIFS([Task transitioned to]:[Task transitioned to], HAS(@cell, "jeff moser")), COUNTIFS([Task transitioned to]:[Task transitioned to], HAS(@cell, "jeff.moser@usaa.com")))


    If this still doesn't work, can you post a screen capture of your sheet, but block out sensitive data? A screen capture with the formula open to show if the colours are highlighting would be useful as well.

    Thanks!

    Genevieve

  • jmo
    jmo ✭✭✭✭✭✭
    edited 04/08/21

    @Genevieve P - I think we're getting closer.

    This one worked for 1 column:

    =SUM(COUNTIFS([Task transitioned to]:[Task transitioned to], HAS(@cell, "jeff moser")), COUNTIFS([Task transitioned to]:[Task transitioned to], HAS(@cell, "jeff.moser@usaa.com")))

    I have 3 columns that I need to look across (and they're right next to each other):

    Column 1: Task transitioned to

    Column 2: Task Transitioned to (People Manager)

    Column 3: Task transitioned to (EMG Owner)

    So I need the formula to look across those 3 columns; Column 1: Column 3.

    Is that possible?


    Really appreciate you sticking with me on this!

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @jmo

    The way I would personally do this is to create three separate COUNTIFS formulas, then add them together! One for each column, like so:

    =SUM(COUNTIFS([Task transitioned to]:[Task transitioned to], HAS(@cell, "jeff moser")), COUNTIFS([Task transitioned to]:[Task transitioned to], HAS(@cell, "jeff.moser@usaa.com")))

    +

    SUM(COUNTIFS([Task Transitioned to (People Manager)]:[Task Transitioned to (People Manager)], HAS(@cell, "jeff moser")), COUNTIFS([Task Transitioned to (People Manager)]:[Task Transitioned to (People Manager)], HAS(@cell, "jeff.moser@usaa.com")))

    +

    SUM(COUNTIFS([Task transitioned to (EMG Owner)]:[Task transitioned to (EMG Owner)], HAS(@cell, "jeff moser")), COUNTIFS([Task transitioned to (EMG Owner)]:[Task transitioned to (EMG Owner)], HAS(@cell, "jeff.moser@usaa.com")))


    For a full formula:


    = SUM(COUNTIFS([Task transitioned to]:[Task transitioned to], HAS(@cell, "jeff moser")), COUNTIFS([Task transitioned to]:[Task transitioned to], HAS(@cell, "jeff.moser@usaa.com"))) + SUM(COUNTIFS([Task Transitioned to (People Manager)]:[Task Transitioned to (People Manager)], HAS(@cell, "jeff moser")), COUNTIFS([Task Transitioned to (People Manager)]:[Task Transitioned to (People Manager)], HAS(@cell, "jeff.moser@usaa.com"))) + SUM(COUNTIFS([Task transitioned to (EMG Owner)]:[Task transitioned to (EMG Owner)], HAS(@cell, "jeff moser")), COUNTIFS([Task transitioned to (EMG Owner)]:[Task transitioned to (EMG Owner)], HAS(@cell, "jeff.moser@usaa.com")))


    Will this work for you?

    Cheers,

    Genevieve

  • jmo
    jmo ✭✭✭✭✭✭

    Wow @Genevieve P - that's quite a novel you've written there! 😋

    I'll give that one a try - was attempting to not have to craft out 3 separate formulas to count these instances but, alas, I will comply.

    This just gets lengthy for me since all 15 of the EMGs will want to know their counts so.....here I go typing away. 😟

    Really appreciate your help on this.

  • I have almost the same problem.


    I am trying to find out how many times the contact "Franck Debucquet" is mentioned in a column.

    Sometimes a cell can contain several contacts.

    See example below

    :

    Thanks !

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Christophe AUBRIOT

    When there are multiple values selected (either in a Contact column or Dropdown list column), you'll want to use the HAS function.

    Try something like this:

    =COUNTIF([Column Name]:[Column Name], HAS(@cell, "Franck Debucquet"))

    or if it's Cross-Sheet:

    =COUNTIF({Column in other sheet}, HAS(@cell, "Franck Debucquet"))


    Let me know if this works for you!

    Cheers,

    Genevieve

  • Jessica Griffin
    Jessica Griffin ✭✭✭✭

    @Genevieve P.

    =COUNTIF({Internal Comms Intake Range 1} HAS(@cell, "Amy Merck"))

    I am counting cross sheet contacts where multiple contacts can be in a list.

    Receiving the unparseable return

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Jessica Griffin

    It looks like you're just missing the comma after the Range but before the HAS critieria:

    {Internal Comms Intake Range 1}, < Comma

    Try this:

    =COUNTIF({Internal Comms Intake Range 1}, HAS(@cell, "Amy Merck"))


    Cheers!

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!