# Lookup with Multi Select Column

Options
✭✭✭

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.

• ✭✭✭✭✭✭
Options

Give this a try:

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

• ✭✭✭✭✭✭
Options

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.

School of Sheets (Smartsheet Partner)

If my answer helped please accept and react w/💡Insightful, ⬆️ Vote Up, ❤️Awesome!

• ✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

Give this a try:

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

• ✭✭✭
Options

Thanks Paul. That worked great.

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!