Kuovonne's Guide to Airtable

Explore

Kuovonne's Guide to Airtable

Kuovonne's Guide to Airtable

Specific Solutions

Sum of Multiple Select choices

⁠

⁠ Tip Jar ⁠ Thank Kuovonne for creating this content! ⁠ ⁠ ⁠

⁠

Inspiration

user on the Airtable community forum⁠

has a multiple select field with several options. Each option has a price.

The user is currently manually adding the price of all the selected options and typing the total in a {Price} currency field.

How can Airtable calculate the total?

Formula method (discouraged)

One option is to use a formula field that checks for the existence of each possible option, and converts the option to a number. Then take the sum.

Note that we use the FIND() function instead of a straight comparison since there could be multiple options.

SUM(

IF( FIND("Option A", {Options}), 10),

IF( FIND("Option B", {Options}), 20),

IF( FIND("Option C", {Options}), 30)

)

⁠

However, this method has several drawbacks.

You must maintain the option names in two places: both the multiple select and the formula field.

Any changes to prices require editing the formula field.

You cannot perform analytics or generate reports on the individual options.

Linked Record method (preferred)

A better option would be to convert the multiple select field to a linked record field. Store the prices in the linked table and use a rollup field.

First convert the multiple select field to a linked record field.

⁠

Then store the price in a currency field in the new table.

⁠

Back in the original table, create a rollup of the prices in the original table.

⁠

Now you have much cleaner system that makes it easier to add, edit, delete, and generally maintain options and prices without editing any formulas.

⁠

Change the pricing in the linked table (or the formula), will change the pricing for all existing records. If you want to preserve historic data, you will need to move the calculated total to an editable field.

⁠

⁠ ⁠ Thank Kuovonne for creating this content! ⁠ Give Kuovonne a sandwich ⁠ Signup for Coda w/ referral link ⁠ ⁠ ⁠Buy from my Gumroad Store ⁠ ⁠ ⁠ ⁠ ⁠

⁠

Gallery

Want to print your doc?
This is not the way.

Try clicking the ⋯ next to your doc name or using a keyboard shortcut (

CtrlP

) instead.