SUMIFS with a Multi-select Contact Column

jennistasiak

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))

Paul Newcome

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)

Paul Newcome

CONTAINS does not work with contacts. You will need to use either the HAS or FIND function.

jennistasiak

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})

Paul Newcome

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)

jennistasiak

Thanks Paul! I finally have it working

Paul Newcome

Happy to help. 👍️