Google-sheets – Add amount IF statement on Google Sheets

formulasgoogle sheets

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!

Pic

Best Answer

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.

Related Topic