Create a formula to calculate the price of comma delimited items in 1 column.

Crown

I have a vendor registration form that connects to Smartsheet from Google Forms using the Smartsheet Add on for Google Forms. When completing the registration form, the vendors can select up to 4 tables to rent and each table may have a different price. The form automatically created and populates my Smartsheet with the Tables information separated by commas as seen below. (The Tables Column is a Text/Number type.). In google sheets, I can use an array formula and it pulls out the number, but Smartsheet doesn't have the Array and I'm completely lost with how to create a formula to pull this data. For this example, I want it to pull everything between the "$" and the ",". So it would be =15+15+7+7 = $44. How can I achieve this?

jmyzk_cloudsmart_jp

Hi @Crown

Since "the vendors can select up to 4 tables to rent," your Google form should use the "Checkboxes" option, which looks like this. (The example form below allows up to six tables for the demo purpose.)

https://forms.gle/Ap3mr8e84KrZrzEd6 (Google Form Link)

From the Google form setting, copy the options to a Smartsheet like this;

https://app.smartsheet.com/b/publish?EQBCT=6fb9c79d315342fd8ceef4edd8e431bd (Link to the published sheet)

The sheet's formula creates a Table Price List.

[Price] =VALUE(RIGHT(Table@row, LEN(Table@row) - FIND("$", Table@row)))

Then, you can add the following formula to the sheet the add-on has created to get the Total Price.

[Total Price] =SUMIFS({Table Price List Range : Price}, {Table Price List Range : Table}, CONTAINS(@cell, Tables@row))

In the formula, @cell inputs a range item, such as "Standard Table #4 - $15", "Trainer Table #5 - $7", etc., from the {Table Price List Range : Table} range to the CONTAINS function's "search_for" value.

For example, the [Tables] value in the first row contains "Standard Table #4 - $15", so the CONTAINS function returns True in the fourth round of {Table Price List Range : Table} evaluation by the @cell, and the SUMIFS function adds 15.

https://app.smartsheet.com/b/publish?EQBCT=caf3b6fa81bf491ab49ac48d0488cd41 (Link to the published sheet)