Sumif formula request

[Muthukrishnan V]

Excel 2007
If A1 is >10000 but <20000, then 3% of D1.
If A1 is >20000 but <30000, then 5% of D1
If A1 is >30000, then 6% D1.



For example:
if A1 is 17000 and D1 is 1000, then 3% of D1: Output is 30.
If A1 is 29000 and D1 is 1000, then 5% of D1: Output is 50.
If A1 is 35000 and D1 is 1000, then 6% of D1: Output is 60.



Request formula.

 

[etaf]

is this just the one cell - sumif() usually adds up a range

otherwise a nested IF or Choose ()
Do you mean A1 less than 1000 not less or equal to ?

=IF( A1 < 1000 , "" , IF ( A1 < 2000 , D1 * 0.03 , IF ( A1 < 3000 , D1 * 0.05 , D1 * 0.06 )))

if you meant less or equal to - just change the < to < =

 

[Muthukrishnan V]

A1 varies from 10000 to 50000.
D1 is constant 1000.
If A1 is >10000 <20000, then 3% of D1
If A1 is >20000 <30000, then 5% of D1
If A1 is >30000, then 6% of D1

 

[etaf]

What happens if its 1000, 2000 or 3000 , the way you have written Nothing
as you have used greater than ? and also less then so,

no value if

1000 = ? %
2000 = ? %
3000 = ? %

 

[Muthukrishnan V]

Sorry, I am not able to communicate properly.



D6 will have a formula.


In D5 the amount 1000 will not change.

Now 3% incentive will be calculated on the amount mentioned in D5 that is 1000*0.03 if A1 is >=10000 <=20000.
(D5*0.03 . when A1>10000 <=20000)


5% will be calculated on D5 that is D5*0.05 if A1>20000 <=30000. (D5*0.05)



F6% will be calculated on D5 if A1 >30000 (D5*0.06)


Finally if A1 is <= 10000, no incentive on D5. D6 should show 0.

 

[higrm]

etaf's formula, with a slight correction works as you want.

=IF( A1 <= 10000 , "" , IF ( A1 <= 20000 , D1 * 0.03 , IF ( A1 <= 30000 , D1 * 0.05 , D1 * 0.06 )))

 

[Muthukrishnan V]

Fine. Can the formula be improved to show 0 when A1<10000

 

[higrm]

just replace the "" with zero in the first part of the IF statement.