Trying to sum the value of multiselect selections

orenwick

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

heyjay

=SUMIF(
Project1, 
CONTAINS(@cell, [Planting Status]@row), 
Total Plots1)   

orenwick

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})),)