# 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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
 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>

wow thank you much appreciated

happy to help - it stops me watching daytime TV.....

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

=(D11 >=5000)*

so what if D11 = 4999

=(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

thanks - I learned something else - great !

Replies
5
Views
2K
Replies
2
Views
437
Replies
0
Views
196
Replies
3
Views
543
Replies
6
Views
886

1,221,418
Messages
6,159,790
Members
451,589
Latest member
Harold14

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back