Sumif formula request

Muthukrishnan V

Board Regular
Joined
May 29, 2008
Messages
120
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

Well-known Member
Joined
Oct 24, 2012
Messages
3,382
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
Joined
May 29, 2008
Messages
120
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
Joined
Oct 24, 2012
Messages
3,382
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
Joined
May 29, 2008
Messages
120
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
Joined
Nov 5, 2014
Messages
47
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 )))
 

higrm

New Member
Joined
Nov 5, 2014
Messages
47
just replace the "" with zero in the first part of the IF statement.
 

Forum statistics

Threads
1,077,991
Messages
5,337,591
Members
399,156
Latest member
RaudMees

Some videos you may like

This Week's Hot Topics

Top