# Is there a shorter way of writing this IF statement?

Options
edited 12/09/19

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::

Tags:

• Options

You may want to create a helper column or two, to break up the formula.  Then just reference that column to shorten the main formula.

• Options

That's a good idea - will definitely try that!

• ✭✭✭✭✭✭
Options

You could also color pallet it. Here is an example I just helped someone with, sometimes the solution isn't really the obvious one.

https://community.smartsheet.com/comment/151026#comment-151026

Basically take all of the variables possible and show what happens when each criteria is met.

• ✭✭✭✭✭✭
Options

You can use that as the first part of an IF, and then in the second part of THAT use your nested IF statement for the changing variables.

=IF(AND([Account Owner]191 = "JK", [SALES REP]191 = "JK", STATUS191 = "Paid"), IF(OR(...............), Then this, IF(OR(.................), then this...............

Basically you are saying IF(AND(all of these things are true), run this nested IF.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!