Skip to main content

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

Here was what my closest effort was:

 

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)

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

 

Any & all help is appreciated!!

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. 

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!

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. 

Reply


Terms & Conditions