SUMIFS with a Multi-select Contact Column
Is there a correct way to add contacts into a formula? I am trying to sum the number of attendees per VP (could be a couple VP's in this multi-select contact column) training request. Do I need to type in the full name? Only part? Add in a special function?
Details:
Completed Projects - Team X # of Attendees = number of attendees per training
Completed Projects - Team X VP = VP correlated to attendee #
"Jenni" = Name in VP column (example)
Formula Tried:
=SUMIFS({Completed Projects - Team X # of Attendees}, {Completed Projects - Team X VP}, CONTAINS("Jenni", @cell))
Best Answer
-
The CONTAINS function doesn't work with contacts, so that second one won't work regardless.
The first one is a bit closer, but you need to make sure the FIND is spelled correctly and use "@cell" for the range portion within the FIND function the same way you did in your original formula using CONTAINS.
Also keep in mind the the FIND function is case sensitive and outputs a number based on where within the evaluated string the string you are searching for is found.
First we correct spelling and syntax:
=SUMIFS({Completed Projects - Team X # of Attendees}, {Completed Projects - Team X VP}, FIND("Jenni",@cell))
If "Jenni" is found in the string, the FIND function will output a number. Lets say 10 for example if "Jenni" starts at the 10th character within the cell. This means your formula now reads:
=SUMIFS({Completed Projects - Team X # of Attendees}, {Completed Projects - Team X VP}, 10)
There is nothing there for it to compare to, so we need to say to sum if the output of the FIND function is greater than zero.
=SUMIFS({Completed Projects - Team X # of Attendees}, {Completed Projects - Team X VP}, 10 > 0)
Which gives us:
=SUMIFS({Completed Projects - Team X # of Attendees}, {Completed Projects - Team X VP}, FIND("Jenni",@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!
Answers
-
CONTAINS does not work with contacts. You will need to use either the HAS or FIND function.
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!
-
Now I'm getting #INVALID OPERATION.
I tried the below - are there any other tips for using contacts? Do I have to type in the full name or email when referring to a contact?
=SUMIFS({Completed Projects - Team X # of Attendees}, {Completed Projects - Team X VP}, FIIND("Jenni",{Completed Projects - Team X VP})
=SUMIFS({Completed Projects - Team X # of Attendees}, {Completed Projects - Team X VP}, CONTAINS("Jenni",{Completed Projects - Team X VP})
-
The CONTAINS function doesn't work with contacts, so that second one won't work regardless.
The first one is a bit closer, but you need to make sure the FIND is spelled correctly and use "@cell" for the range portion within the FIND function the same way you did in your original formula using CONTAINS.
Also keep in mind the the FIND function is case sensitive and outputs a number based on where within the evaluated string the string you are searching for is found.
First we correct spelling and syntax:
=SUMIFS({Completed Projects - Team X # of Attendees}, {Completed Projects - Team X VP}, FIND("Jenni",@cell))
If "Jenni" is found in the string, the FIND function will output a number. Lets say 10 for example if "Jenni" starts at the 10th character within the cell. This means your formula now reads:
=SUMIFS({Completed Projects - Team X # of Attendees}, {Completed Projects - Team X VP}, 10)
There is nothing there for it to compare to, so we need to say to sum if the output of the FIND function is greater than zero.
=SUMIFS({Completed Projects - Team X # of Attendees}, {Completed Projects - Team X VP}, 10 > 0)
Which gives us:
=SUMIFS({Completed Projects - Team X # of Attendees}, {Completed Projects - Team X VP}, FIND("Jenni",@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!
-
Thanks Paul! I finally have it working
-
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!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 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
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!