Please see test file at this link. I have the following formula on a cell:

`=IFERROR(IFS(AND(L5>=60%,L5<=79.99%),J5*132,L5>=80%,J5*150,\$L\$9>=80%,+2500),0)`

For example, since L5 is 57% (no amt applied) but L9 is at 80%, it should add 2500.

Likewise, cell L6 is at 71% (multiplies amt in J6 by 132 = 660) but L9 is at 80%; it should be a total of 3160 and not 660 by itself.

The formula works perfectly but when it reaches the `+2500` it doesn't add that amount if the cell L9 is over 80%. What could be wrong?

`=IFERROR(IFS(AND(L5>=60%,L5<=79.99%),J5*132,L5>=80%,J5*150,\$L\$9>79.99%,M5+2500),0)`

This is giving me a #REF!

Put the "if \$L\$9>79.99% add 2500" as an outer IF:

``````=IFERROR(
IF(
\$L\$9>79.99%,
IFS(AND(L5>=60%,L5<=79.99%),J5*132,L5>=80%,J5*150)+2500,
0),
0
)
``````

NOTE: The formula on E5 of the linked spreadsheet returns 0 because there isn't a match on IFS as 57.14% is not included on the IFS conditions. You have to decide if you add a condition to handle L5<60% or to replace the second argument of IFERROR.

• That actually worked! It did add the 2500 to the existing amount, but only on the ones that had \$660. It should also add it to the ones that even have \$0. Aug 3 '18 at 14:02
• @A.C. Create a demo spreadsheet including a column with the correct result for each case, share the spreadsheet with anyone with the link to view only and add the link to the question.
– Rubén
Aug 3 '18 at 14:06
• Created and added to the question. Thanks Ruben. Aug 3 '18 at 14:23