Cost Calculation with Multi-Select Dropdowns

MelaMicroKat

I have an intake sheet for samples received for testing in my lab. I originally set up a multi-select drop down column where all requested tests could be selected. Originally the sheet was set up to track studies submitted over time, but now management wants to incorporate cost into my dashboards. I'm trying to figure out the best way to do this. I know how much each assay costs, but I'm trying to figure out the best way to get smartsheets to automatically calculate overall cost based on assays selected.

It would be most convenient to be able to have the calculation performed in the intake sheet, but I couldn't come up with a convenient way to do that. So instead I tried tweaking some of the portfolio metric sheets that I had set up for submission category numbers. I tried to use if contains, but it yielded 1 regardless of which value I tried.

This is the formula I tried: =COUNT(COLLECT({Project ID}, {Requested Tests}, CONTAINS("TAPC")))

The formula is referencing the intake sheet, which I have a picture of below.

Is it possible to reference values from a multi-select dropdown column? If it is, then what I am doing wrong? If it isn't, does anyone have any better suggestions for how to set up a formula to calculate the overall cost? There are a total of 10 possible tests which range in price from $21.69 to $140, and submissions can have almost any combination of the tests.

jessica.smith

@MelaMicroKat

Here is one option. The formula isn't super elegant so if anyone can build on this please do.

Create a price sheet with one column for Test and one column for Price per Sample. This way you can check and modify prices as needed without updating your column formula. See the note below about updating prices.

Create two cross-sheet references on your intake sheet referencing the two columns on the Price Sheet.

e.g., {PriceSheet_Test} and {PriceSheet_Price}

Add the following column formula to your Assay Cost column on the intake sheet:

=IF(CONTAINS("Test 1", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 1"), 1) * [# Samples]@row) + IF(CONTAINS("Test 2", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 2"), 1) * [# Samples]@row) + IF(CONTAINS("Test 3", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 3"), 1) * [# Samples]@row) + IF(CONTAINS("Test 4", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 4"), 1) * [# Samples]@row) + IF(CONTAINS("Test 5", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 5"), 1) * [# Samples]@row) + IF(CONTAINS("Test 6", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 6"), 1) * [# Samples]@row) + IF(CONTAINS("Test 7", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 7"), 1) * [# Samples]@row) + IF(CONTAINS("Test 8", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 8"), 1) * [# Samples]@row) + IF(CONTAINS("Test 9", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 9"), 1) * [# Samples]@row) + IF(CONTAINS("Test 10", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 10"), 1) * [# Samples]@row)

If you want to add a test, you'll need to update your Price Sheet and your column formula.

NOTE: If you think prices are going to need to be updated periodically, I'd recommend including a mechanism to distinuish between active and expired prices in your solution. If you just update the price table without including this, you are going impact the Assay Cost calculation of previous samples.

One way to do this would be to add a Sample Submission Date on your intake sheet and to add an Effective Date and Expiration Date column to your Pricing Sheet. You would then include a date criteria in your Index(Collect) formulas.

jessica.smith

@MelaMicroKat

Here is one option. The formula isn't super elegant so if anyone can build on this please do.

Create a price sheet with one column for Test and one column for Price per Sample. This way you can check and modify prices as needed without updating your column formula. See the note below about updating prices.

Create two cross-sheet references on your intake sheet referencing the two columns on the Price Sheet.

e.g., {PriceSheet_Test} and {PriceSheet_Price}

Add the following column formula to your Assay Cost column on the intake sheet:

=IF(CONTAINS("Test 1", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 1"), 1) * [# Samples]@row) + IF(CONTAINS("Test 2", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 2"), 1) * [# Samples]@row) + IF(CONTAINS("Test 3", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 3"), 1) * [# Samples]@row) + IF(CONTAINS("Test 4", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 4"), 1) * [# Samples]@row) + IF(CONTAINS("Test 5", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 5"), 1) * [# Samples]@row) + IF(CONTAINS("Test 6", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 6"), 1) * [# Samples]@row) + IF(CONTAINS("Test 7", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 7"), 1) * [# Samples]@row) + IF(CONTAINS("Test 8", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 8"), 1) * [# Samples]@row) + IF(CONTAINS("Test 9", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 9"), 1) * [# Samples]@row) + IF(CONTAINS("Test 10", [Requested Tests]@row), INDEX(COLLECT({PriceSheet_Price}, {PriceSheet_Test}, "Test 10"), 1) * [# Samples]@row)

If you want to add a test, you'll need to update your Price Sheet and your column formula.

NOTE: If you think prices are going to need to be updated periodically, I'd recommend including a mechanism to distinuish between active and expired prices in your solution. If you just update the price table without including this, you are going impact the Assay Cost calculation of previous samples.

One way to do this would be to add a Sample Submission Date on your intake sheet and to add an Effective Date and Expiration Date column to your Pricing Sheet. You would then include a date criteria in your Index(Collect) formulas.

MelaMicroKat

That worked! Thank you!