SOLVED

Formula Trouble

Contributor

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)

5 Replies
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)
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?
best response confirmed by erin-5304 (Contributor)
Solution

@erin-5304 

As variant

= LET( s, SUM(E36:I36), IF( s, IF( s <=5000,1500, s*J36), 0 ) )
This worked great! Thank you!

@erin-5304 , you are welcome