Help

Re: IF formula for calculating total cost w/ discount if Multi-select answer is chosen

Solved
Jump to Solution
1267 1
cancel
Showing results for 
Search instead for 
Did you mean: 
tarincook
4 - Data Explorer
4 - Data Explorer

Hello! I'm new to Airtable formulas & could use some much needed guidance! 

I need to calculate the total with an option of a discount if a multi-select answer is chosen. For context, this is in reference to grades of elementary curriculum books purchased. I am trying to create a formula that will show me:

  • If Rural Discount multi-select = 'Small school (one classroom per grade level)' answer, then total # of grades licensed x $250 + additional invoice amount= total invoice cost
  • If Rural Discount multi-select does NOT equal 'Small school (one classroom per grade level)' answer, then total # of grades licensed x $500 + additional invoice amount= total invoice cost

Screenshot 2023-11-14 at 5.48.01 PM.png

Here was what my closest effort was:

Screenshot 2023-11-14 at 6.04.03 PM.png

Additional info:

I'm not sure if this will make a difference/make things more difficult but the column I need to multiple from (total # of grades licensed) also has a formula. Not sure how Airtable is with multi-formula mingling so just wanted to throw that out there! (side note: If there is an easier formula for this, I am also open to suggestions! This was the easiest way I found to total the multi-choice for grades selected)

Screenshot 2023-11-14 at 5.58.17 PM.png

  • "additional invoice amount" is generated on jotform prior to integration so it does not utilize the formula feature. 

 

Any & all help is appreciated!!

1 Solution

Accepted Solutions
Databaser
12 - Earth
12 - Earth

Ah yes, I forgot about that! Try: 

IF(AND({Rural Discount?}="Small school", {Total # of Grades Licensed}>0), ({Total # of Grades Licensed}*250)+{additional invoice cost}, IF({Total # of Grades Licensed}>0, ({Total # of Grades Licensed}*500)+{additional invoice cost}))

Don't forget to mark this as a solution, so others can find it too. 

See Solution in Thread

3 Replies 3

For the calculation of the total incoive cost, try:

IF(AND({Rural Discount?}="Small school", {Total # of Grades Licensed}>0), {Total # of Grades Licensed}*250, IF({Total # of Grades Licensed}>0, {Total # of Grades Licensed}*500))

Using the value of a formula field in another formula field is no problem. 

This worked perfect! Thank you! It didn't add the "additional invoice cost" to the total but I will try to add that to the formula you created. You were a huge help!

Databaser
12 - Earth
12 - Earth

Ah yes, I forgot about that! Try: 

IF(AND({Rural Discount?}="Small school", {Total # of Grades Licensed}>0), ({Total # of Grades Licensed}*250)+{additional invoice cost}, IF({Total # of Grades Licensed}>0, ({Total # of Grades Licensed}*500)+{additional invoice cost}))

Don't forget to mark this as a solution, so others can find it too.