Help

The Community will be undergoing maintenance from Friday February 21 - Friday, February 28 and will be "read only" during this time. To learn more, check out our Announcements blog post.

Re: HELP WITH FORMULAS

459 0
cancel
Showing results for 
Search instead for 
Did you mean: 
lukassouza
4 - Data Explorer
4 - Data Explorer

I am trying to build a table to calculate pricing. But, every column (which is its own product) has different price points under it, based on square footage. How can I set up a formula that will calculate each drop-down option (multiple selection), per column and total that up at the end? See screenshot for reference

First column is interior photos. There are four potential options in that field, so I need a formula that will calculate based on the selection I make in each column. And the same goes for every other one. 

2 Replies 2
Mike_AutomaticN
10 - Mercury
10 - Mercury

Hey @lukassouza!

I'm having a hard time to fully understand your use case. But at plain sight, the Data Base Architecture used (one table) and the field types (e.g. single select for pricing) do not look great.

I'd love to provide some further insights and guidance. Would you please describe a bit further what it is that you are trying to achieve as well as how the business works (for further context).

Mike, Consultant @ Automatic Nation

Kenneth_Raghuna
8 - Airtable Astronomer
8 - Airtable Astronomer

Hey Lukas,

Mike is correct in stating that the base architecture could use some work.

You can technically achieve what you are trying to do with the current setup, but the formula would be VERY long and not very robust (changing any of the multi-select option names would break it).

Here's the quickest way you can get to where you are trying to go with minimal changes to the current design:

Create a second table that will contain all of the services offered. Let's call it "Services." The primary field is the "Service," another field is a currency field called "Cost." You should additionally add a single select field to categorize what the service relates to (ie. Interior Photos, Exterior Photos, etc), let's call it "Service Type." In the example I made below, I made the "Service" field a formula field that creates its name based on the Service Type and Size fields.

Then in your first table, you can create a linked record field for each service type. Configure each field so that only fields of that service are linkable. For each field, add a lookup field for the Cost.

Then, you can create a formula field that adds all of the lookup fields together to get your final cost. You can format the formula output as a currency so that it shows in dollars.

This workflow would look a lot better in an interface rather than the Data layer. You also may want to change the base design to something even more different depending on the full scope of your business operations that you are trying to encapsulate.