# Sumif formula request

#### Muthukrishnan V

##### Board Regular
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.

### Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

#### etaf

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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 = ? %

Last edited:

#### Muthukrishnan V

##### Board Regular
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

##### New Member
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

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

#### higrm

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