Trying to sum the value of multiselect selections
Hi Smartsheet Community,
I have a project built out where as a team finishes a project they can submit a form for that project which will populate a dashboard of graphs to give leadership progress report.
They want to be able to select more than one project completed at once so that they can input less forms, I'm right there with them.
I have a reference sheet that all my equations are based off of which has every possible combination of project and location and the total products to be completed from that project at that location. phew. See below the reference sheet showing just one project and the number of total possible plots at each location for that project.
How do I sum the number of plots completed for a location depending on how many projects where selected as completed in the multi select?
Here is the equation I tried but got unparseable. I have clicked through it a bunch of times looking for a formula error but am not seeing it.
=IF([Planting Status]@row = "Fully Planted", SUMIFS({Total Plots1}, {Location1}, Location@row, {Project1}, HAS({Project1}, Project@row)), )
Any help is greatly appreciated I feel like I am very close and am just not understanding something about how they nest.
Thank you,
Olive
Answers
-
=SUMIF( Project1, CONTAINS(@cell, [Planting Status]@row), Total Plots1)
...
-
According to the =SUMIF syntax the first variable would have to be the range I'm returning which would be Total Plots1. I want to add together the total plots of the projects selected.
SUMIFS(
Range returned = Total Plots1
Criterion Range1 = Location range on reference sheet
Criterion1 = location@cell
Criterion Range2 = Project range on reference sheet
criterion2 = project@cell to narrow down my question can criterion be multiple values?
I just tried to swap has for contains and am still getting unparseable error.
=IF([Planting Status]@row = "Fully Planted", SUMIFS({Total Plots1}, {Location1}, Location@row, {Project1}, CONTAINS(Project@row,{Project1})),)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 304 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!