How to count contacts or email address in contact column

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?

James Keuning

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.

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

James Keuning

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

@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

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.

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

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

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

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

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

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.

Christophe AUBRIOT

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.

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

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

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