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.
Best Answer
-
Give this a try:
=SUMIFS({$$ Column}, {Description Column}, HAS(Investments@row, @cell))
Answers
-
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!
-
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.
-
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
-
Give this a try:
=SUMIFS({$$ Column}, {Description Column}, HAS(Investments@row, @cell))
-
Thanks Paul. That worked great.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!