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)
Answers
-
CONTAINS does not work with contacts. You will need to use either the HAS or FIND function.
-
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)
-
Thanks Paul! I finally have it working
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!