Lookup with Multi Select Column

mikeb

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.

Paul Newcome

Give this a try:

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

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