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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • 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
    Paul Newcome ✭✭✭✭✭✭
    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Thanks Paul! I finally have it working

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!