Is there a shorter way of writing this IF statement?

Annie Dunn
edited 12/09/19 in Formulas and Functions

Here's the working statement:

=IF(AND([Account Owner]191 = "JK", [SALES REP]191 = "JK", OR([BILLED BY]191 = "Merit Medical", [BILLED BY]191 = "Don Joy"), STATUS191 = "Paid"), CHARGES191 * 0.14, IF(AND([Account Owner]191 = "JK", [SALES REP]191 = "JK", OR([BILLED BY]191 = "Cerapedics", [BILLED BY]191 = "Surgentec", [BILLED BY]191 = "InfinityDMEDewanjee", [BILLED BY]191 = "InfinityDME"), STATUS191 = "Paid"), CHARGES191 * 0.2))


Right now I'm repeating this part: IF(AND([Account Owner]191 = "JK", [SALES REP]191 = "JK", STATUS191 = "Paid") every time. (This part of the statement always need to be true.) 

The pieces of the equation that change are the [BILLED BY] field and corresponding value.

So, assuming all of the above statements are true, I need the following calculations to occur:

[BILLED BY] = "Don Joy", [BILLED BY] = "Merit Medical" then [CHARGES] * 14.

[BILLED BY] = "Cerapedics", [BILLED BY] = "InfiniteyDMEDewanjee", [BILLED BY] = "Surgentec" then [CHARGES] * .2

[BILLED BY] = "NuTech", [BILLED BY] = "Instrumentation" then [CHARGES] * .15

[BILLED BY] = "Elliquence" then [CHARGES] * .125


Please let me know if there's a more concise way to write this this statement, because I also need to repeat this statement for 5 different Sales Reps + Account owner combos with varying value calculations. ::Sigh::



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!