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

Nate Benn

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

Paul Newcome

Give this one a go...

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

Paul Newcome

Give this one a go...

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

Eric M Oliveira

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.

HAS: https://help.smartsheet.com/function/has

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.

Cheers,

Eric

Smartsheet Technical Support

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

a...25

b...50

c...15

d...10

you would have to have

a...25

b...50

c...15

d...10

ab...75

ac...40

ad...35

bc...65

bd...60

cd...25

abc...90

abd...85

bcd...75

abcd...100

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.

Nate Benn

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

Happy to help! 👍️