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

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?

• ✭✭✭✭✭✭

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.)

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

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.