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

Find more posts tagged with

Formulas

functionality

Accepted answers

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

All comments

Paul Newcome

Give this one a go...

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! 👍️

Elizabeth N

Hi @Paul Newcome … I stumbled into this thread and am attempting to do something similar but am returning a "#NOMATCH". Any chance you are able to check out my post and help me figure out what I'm missing? Thank you!!!

https://community.smartsheet.com/discussion/comment/481351#Comment_481351

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.