Skip to main content

Thank you for taking your time to look at this.


The company I work for offers services that are modular, so not all clients are going to request the same services. The client will pick and choose the services that they need. To help automate the quotes we give for our pricing, I’m trying to use a formula field to calculate the final cost, however I’m running into problems.


As of right now, I want to use multiple single select fields with the name of the service with the options of selecting YES or NO


I’ve written up the following formula. IF({Service 1}=“Yes”,13,0)


I can assign a dollar amount to the service.The problem I’m running into, is getting all the services to add together to give one total amount.


What I would like to do is,


1. Give each service a dollar amount.


2. Then using that number, calculate all the services the client has requested into one final total that uses the currency format.


Lets pretend that there are 3 services. Service 1 is $13. Service 2 is $67. Service 3 is $89. Depending on if the client has selected YES or NO that will change the total amount in the formula field. YES being the dollar amount NO being zero.


Here’s a screen grab of an example of what I would like to happen. Price would be the formula field and not the single text line shown in the photo.


So for each client the price would be


Client 1 Price- $169

Client 2 Price - $67

Client 3 Price - $102


Using formulas, how do I turn word responses into number amounts that can be calculated together.



Any thoughts? Does that make sense?

Welcome to the community, @Jay_uku! :grinning_face_with_big_eyes: Yes, that totally makes sense. Thanks for sharing such a detailed explanation of your issue!


The sample formula you wrote above is a great starting place. However, you can actually make it a tad shorter and drop the 0 from the end. Airtable defaults to a BLANK()-equivalent value if that last part is omitted. Because the main value returned is a number, that BLANK()-equivalent value will automatically be 0.


With that in mind, all you need to do is chain a series of those together—one for each single-select field—and add the values returned from each IF() function:


IF({Service 1} = "YES", 13) + IF({Service 2} = "YES", 67) + IF({Service 3} = "YES", 89)

Format the field as currency, and you’re all set.


Welcome to the community, @Jay_uku! :grinning_face_with_big_eyes: Yes, that totally makes sense. Thanks for sharing such a detailed explanation of your issue!


The sample formula you wrote above is a great starting place. However, you can actually make it a tad shorter and drop the 0 from the end. Airtable defaults to a BLANK()-equivalent value if that last part is omitted. Because the main value returned is a number, that BLANK()-equivalent value will automatically be 0.


With that in mind, all you need to do is chain a series of those together—one for each single-select field—and add the values returned from each IF() function:


IF({Service 1} = "YES", 13) + IF({Service 2} = "YES", 67) + IF({Service 3} = "YES", 89)

Format the field as currency, and you’re all set.



OH. wow…


I was making that way more complicated. 😫

Perfect! Thank you so much!


I’m loving Airtable. Thanks again. Looking forward to getting to know this community.


Asides from Airtable’s references guides, would you recommend any other resources to understand how formulas work?

My pleasure, Jay! Glad to know that you got the answer you were seeking! If you would, please mark my comment above as the solution to your question. This helps others who may be searching with similar questions. Thanks!


Regarding other resources for understanding formulas, I’d say this community is one of the best. Many of us try to not just post formulas, but explain how they operate, so you could glean a lot by just reading threads for a while.


I’m also planning to delve deeper into formulas on my fairly-new YouTube channel that’s dedicated to all things Airtable. Subscribe if you want to see what’s coming!

Reply


Terms & Conditions