Forum Discussion

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 500...

excel

Formulas and Functions

SergeiBaklan
Apr 22, 2022
erin-5304

As variant

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

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
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
  Diamond Contributor
  Apr 22, 2022
  erin-5304
  
  As variant
  
  = LET( s, SUM(E36:I36), IF( s, IF( s <=5000,1500, s*J36), 0 ) )
  - erin-5304
    Brass Contributor
    Apr 22, 2022
    This worked great! Thank you!