Forum Discussion

erin-5304's avatar
erin-5304
Brass Contributor
Apr 22, 2022
Solved

Formula Trouble

Here is the situation:

 

If E36:I36 are empty, then the cell stays blank; if E36:I36 add to greater than 5000, then that sum needs to multiply by .40 cents; if that sum is less than or equal to 5000, it should be 1500.

 

This is what I have but it isn't working, where do i have it wrong?

= IF(E36:I36="","0"),IF(XOR(E36:I36<="5000"),"1500",SUM(E36:I36)*J36)

excel
Formulas and Functions

5 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Apr 22, 2022
    your first IF() is a complete formula and then you added "," and more which isn't allowed. Try this:
    = IFS(E36:I36="",0,SUM(E36:I36)<=5000,1500,TRUE,SUM(E36:I36)*J36)
    • erin-5304's avatar
      erin-5304
      Brass Contributor
      Apr 22, 2022
      It looks like it is coming up with the correct answers, but it is populating a bunch of cells to the right with possible answers, it says #SPILL. How do I make sure it only shows the correct answer in one cell?
      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Apr 22, 2022

        erin-5304 

        As variant

        = LET( s, SUM(E36:I36), IF( s, IF( s <=5000,1500, s*J36), 0 ) )

Resources