How to count contacts or email address in contact column
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
-
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:
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
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:
-
@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!
-
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?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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?
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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!
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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 !
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
=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
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 464 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 61 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!