COUNTIF Formula for Contact Column
I have several tasks set up for team members using their email addresses in the Contact column. Some tasks have 1 member assigned and some have multiple. I have to run each person's workload and am trying to use a separate sheet that counts the number of times their email address is in the Contact column. However, the COUNTIF formula only counts them if there is only 1 person assigned in the Contact column. If there is more than 1 email address, the formula counts it as "0", even if their email address is in the cell. The goal is to have the workload calculate in "real time" in this sheet, using the COUNTIF formula, and be linked to the master sheet where the source data is located.
I've already submitted a product enhancement request, but need a workaround in the interim! Any ideas would be appreciated.
Comments
-
Hi Mel,
I think I have something that will help. I'll get back to this post a little later.
Have a fantastic day!
Best,
Andrée Starå
Workflow Consultant @ Get Done Consulting
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
You should be able to incorporate the FIND function into the criteria of your COUNTIFS instead of looking for a match.
If your current formula looks like this which counts only if the email is the only one in the cell:
=COUNTIFS({Email Address Column}, "address@address.com")
then you would want to try something along the lines of this:
=COUNTIFS({Email Address Column}, FIND("address@address.com", @cell) > 0)
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
This doesn't seem to work when I'm referencing a Contact column in another sheet? It does work when I reference a range within the same sheet I'm doing the countifs in. Any ideas?
-
What is your current formula?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
=COUNTIFS({Contact Column}, FIND("address@address.com", @cell) > 0)
Also tried this:
=COUNTIFS({Contact Column}, FIND("Person Name", @cell) > 0)
-
And you are following the correct steps to select the appropriate range for your cross sheet reference?
Are you typing in the correct email address? Spelling, punctuation, and upper/lower case all have to match.
It is working for me.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
WORKING NOW!
Thanks!!!
-
Excellent! Happy to help!
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
How do you make this work if you have other criteria that you want to be present, in addition to the "find" criteria. I'm trying to only count if all 4 of the criteria are present, (issue=project, progress = not full, status = yes and a particular person is in the Leader column) but it seems like it's not taking into account all the criteria, but I can't figure out how it's getting the 82 to figure out my error.
Here's my formula:
=COUNTIFS({Issue Type}, "Project", {Progress}, "<> Full", {Status}, "Yes", {Leader Responsible Party}, (FIND("Person 1", @cell) > 0))
I'm expecting 21 (hand count) and it's showing 82. I would expect 130 if I as looking for a full status.
Any suggestions?
-
Hi @ialarson
There are a few syntax elements here to adjust. The quotes for your second criteria should only be around "Full" and not the <> symbol. I would also personally use the HAS function to search for a person within the contact column if it's multi-select, like so:
=COUNTIFS({Issue Type}, "Project", {Progress}, <> "Full", {Status}, "Yes", {Leader Responsible Party}, HAS(@cell, "Person 1"))
Let me know if this returns the correct result!
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!