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?
Accepted Solutions
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.
