How to add multiple costs from dropdown menu items

Question

Hello,

I am struggling to identify a formula that can read multiple selections in a drop down column, find the corresponding selections and prices on another sheet, and return a total cost.  The sheet supports samples that are analytically tested. The user chooses which tests they want to conduct for each sample on the "Tracking Sheet", and the sheet can pull that data from the "Pricing Sheet" and return the estimated cost on the "Tracking Sheet".

The problem I'm encountering is that there are 54 possible selections with prices for each one. I tried using an IF INDEX COLLECT formula but it is too long and I run into the character limit. I need to find a simpler formula that can read the "test set" column, find the corresponding prices based on the selections, and return the sum value in the "estimated cost" column.

Here's the dropdown column that allows the user to choose the tests to run per sample on the tracking sheet:

Here's a screenshot of a section of the pricing sheet:

Here's a screenshot of the estimated cost column in the tracking sheet:

And here's a screenshot of the formula that is too long:

DKazatsky2 · Accepted Answer

Hi @mwfhills23,

Try this.

=SUMIF({Request Set}, HAS([Test Set]@row , @cell ), {Price})

Hope this helps,

Dave

mwfhills23 · Accepted Answer

I appreciate the help, but this was solved by another user with the following formula:

=SUMIFS({Price}, {Request Set}, HAS([Test Set]@row , @cell ))