Lookup with Multi Select Column

Hi. I have a Smartsheet of "capabilities". For each capability, someone can select multiple "investments" that support that capability. So you might have:

Capability1: MyCapability

Investments: App1 Hardware1

Each capability could have 1 or dozens of investments. I have another sheet with these investments in it and their cost

App1 $10

Hardware1 $20

I want to look up each investment for MyCapability and return the sum of the total cost $30.

Is this possible?

Any help would be much appreciated.

Find more posts tagged with

Accepted answers

Paul Newcome

Give this a try:

=SUMIFS({$$ Column}, {Description Column}, HAS(Investments@row, @cell))

All comments

SoS | Dan Palenchar

Hello @mikeb,

Can you post a Screenshot of your Sheet? I'm trying to figure out how your columns are structured so that I can answer this.

mikeb

Sure. Here you go:

Main Sheet

Investment Sheet

So I want to lookup the investments listed in the main sheet that are in the investment sheet and return the sum of the total expense.

Thanks.

DKazatsky2

Hi @mikeb,

Give this a try.

=SUMIF({Itemization Description},CONTAINS(@cell, Investments@row), {Total Expense})

{Itemization Description} = cross-sheet reference to Itemization Description column

{Total Expense} - cross-sheet reference to Total Expense column

Hope this helps.

Dave

Paul Newcome

Give this a try:

=SUMIFS({$$ Column}, {Description Column}, HAS(Investments@row, @cell))

mikeb

Thanks Paul. That worked great.

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.