How to use multi-select dropdown list as criteria for sumif/vlookup

Hey guys, thanks in advance for you help.

I have three sheets that I am working with: a database (of sorts), a grid-form summary sheet, and sheet that contains attributed values. To simplify, I will only use a, b, c, and d as production point names and whole percentages as attributed values.

The "database" contains unique project names each with a multi-select dropdown list that have any combination of a, b, c, or d production points. Each of these production points are associated with a certain percentage found in a separate sheet. In the summary sheet (internal, created summary sheet, not Smartsheet's Summary feature) I would like to return the combined value of the attributed percentages based on the multi-select in the database and the similar project names between the database and summary sheet.

I am currently trying to use a mix between a sumif and vlookup and/or a match/collect but I am having trouble getting anywhere. I have attached pictures of the examples.

Attachment 1: "Database"

Attachment 2: Summary sheet with desired result and explanation

Attachment 3: Attributed Values

Best Answer


  • Hi Nate,

    Paul's example looks like it would achieve your desired goal. Something to consider if you're looking for an exact match within a multi-select dropdown or contact list it may be best to utilize a HAS instead of a CONTAINS function. We've found that the contains function in some cases will include values that contain similar characters when summing or counting, I've personally found this more with contact values for some reason.The below Help Center article outlines HAS in further detail.


    It's the same syntax as the contains but in reverse. Thanks again Paul, great job.

    =SUMIFS({Attributed Values Sheet - Attributed Value Column}, {Attributed Values Sheet - Production Point Column}, HAS(@cell, INDEX({Database Sheet - Multi-select Column}, MATCH([Target Sheet Project Column]@row, {Database Sheet - Project Name Column}, 0))))

    Have a wonderful day. Thank you for contacting Smartsheet Support.



    Smartsheet Technical Support

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Eric M Oliveira

    The reason I used CONTAINS was because we are looking for a specific value within multiple values in the multi-select field. That is also why I used the @cell reference in the first portion of the function.

    To use the HAS function, the reference table would have to have every single variation of selectable options listed out. So instead of having





    you would have to have















    And that is just for 4 options. If there are 10 different options and any combination is possible, that table would be huge and very difficult to manage to the HAS function whereas the CONTAINS allows you to list out each option one time and pull them all together.

  • Thanks so much Paul and Eric! Y'all helped a ton. Paul, I found myself going down a similar logic path with the HAS function.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!