Calculating SUM based of criteria from multiselect.

Colin Laurencelle

Good day,

I have a form with quite a few multi selects. After a user selects all that apply, I want to cross reference what he selected with a second sheet that has "costs values" associated to the same text that the user had available to him in the multi select drop down on the form.

So basically after the user fills out the form. The sheet now contains multiple values, in multiple columns.

I need to get a total cost (sum) of everything the user selected between all columns. The cost values are on a different sheet.

Hope I explained that well enough lol 🤣

Any help or guidance would be much appreciated.

Paul Newcome

Are you able to provide some screenshots for reference?

Colin Laurencelle

For sure,

So this is my first sheet. The goal is to gather the sum of all the selections in the Location columns based off the second sheet...

*Please note that I adjusted some data to keep proprietary information private. (Part 1, 2 ,3 ).

Second sheet with pictures:

Paul Newcome

Is it possible that "Part 1" would be in both Location A and Location B?

Colin Laurencelle

Yes! It certainly is. In this case, I would need part 1's cost to be added every time. There could technically be a part 1 at every possible location.

Paul Newcome

Try something like this...

=SUMIFS({List Sheet Price Column}, {List Sheet Type Column}, HAS([Location A - Damage Type]@row, @cell))

Then replicate this for Location B and add them together.

=SUMIFS({List Sheet Price Column}, {List Sheet Type Column}, HAS([Location A - Damage Type]@row, @cell)) + SUMIFS({List Sheet Price Column}, {List Sheet Type Column}, HAS([Location B - Damage Type]@row, @cell))

Colin Laurencelle

I think that worked Paul, you're the man.

Thanks so much.

Paul Newcome

Happy to help. 👍️