# Sum if greater than but less than

#### turnej

##### New Member
Hi Guys,

I need to calculate the values of a particular cell if certain conditions are met - I will try and explain

If C11=12000 then the sum will be 30% of the value between 5000 and l0000 and 50% of the value above 10000

eg.

0-5000=0
5000-10000*30%=1500
2000*50%=1000

Formula result 3000

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

#### oldbrewer

##### Board Regular
 Hi Guys, I need to calculate the values of a particular cell if certain conditions are met - I will try and explain If C11=12000 then the sum will be 30% of the value between 5000 and l0000 and 50% of the value above 10000 eg. 0-5000=0 5000-10000*30%=1500 2000*50%=1000 Formula result 3000 12000 2500 formula is =IF(D18<5000,0,IF(D18<10000,(D18-5000)*0.3,5000*0.3+(D18-10000)*0.5))

<colgroup><col span="13"></colgroup><tbody>
</tbody>

• turnej

#### turnej

##### New Member
wow thank you much appreciated

#### oldbrewer

##### Board Regular
happy to help - it stops me watching daytime TV.....

#### West Man

##### Well-known Member

Another way, slightly shorter without IF: =(D11 >=5000)*(((D11-5000)*0.3)+MAX(0,(D11-10000)*0.2))

#### oldbrewer

##### Board Regular
=(D11 >=5000)*

so what if D11 = 4999

#### West Man

##### Well-known Member
=(D11 >=5000)*

so what if D11 = 4999

Then that portion should evaluate to false, be coerced to zero and when multiplied by the rest of the formula would result in zero

#### oldbrewer

##### Board Regular
thanks - I learned something else - great !

Replies
5
Views
42
Replies
16
Views
144
Replies
12
Views
108
Replies
4
Views
91
Replies
0
Views
62