# Need Formula to display commissions earned per sale when commission rate is tiered

#### Urban1323

##### New Member
Our salesperson makes 5% of every sale until she reaches \$20,000 in total sales. At that point the commission jumps to 10% on everything over \$20,000. I found a great sum product formula that will tally the total sales commission correctly and will tally individual sales until the tier level switch (see below)
=SUMPRODUCT((B10<=\$C\$4:\$C\$5)*(B10>\$B\$4:\$B\$5)*(B10-\$B\$4:\$B\$5)*\$D\$4:\$D\$5)+SUMPRODUCT(((B10>\$C\$4:\$C\$5)*(\$C\$4:\$C\$5-\$B\$4:\$B\$5))*\$D\$4:\$D\$5)

I need a way to show commissions earned per sale. The cells outlined in red show where the above formula stops working.

Appreciate any help I can get on this. Thanks in advance!

#### Attachments

• 04D0F101-4DBB-4050-B09A-82DCFCB4831C.jpeg
229.4 KB · Views: 11

### Excel Facts

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Welome to the Forum!

Here's one way to do it:

ABC
1RateThreshold
2Rate15%
3Rate210%20,000
4
5TotalSaleCommission
610,00010,000500
712,0002,000100
815,0003,000150
919,0004,000200
1025,0006,000550
1132,0007,000700
Sheet1
Cell Formulas
RangeFormula
A6A6=B6
A7:A11A7=A6+B7
C6:C11C6=Rate1*A6+(Rate2-Rate1)*MAX(0,A6-Threshold)-SUM(C\$5:C5)
Named Ranges
NameRefers ToCells
Rate1=Sheet1!\$B\$2C6:C11
Rate2=Sheet1!\$B\$3C6:C11
Threshold=Sheet1!\$C\$3C6:C11

Works perfect! Thank you. I never cease to be impressed with the brain power on this forum.

Replies
9
Views
519
Replies
30
Views
2K
Replies
1
Views
368
Replies
9
Views
368
Replies
2
Views
206

1,203,082
Messages
6,053,419
Members
444,662
Latest member
AaronPMH

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