# Tiered cumulative commission by unit

#### Theprofessorlg

##### New Member
Please help me, I have been working on this for a while and cannot figure it out. This is what I need. My company is doing a tiered cumulative commission by unit.

Units sold 1-3 commission is \$50 per
Units sold 4-6 commission is \$75 per
Units sold 7+ commission is \$100 per

In January a customer buys one unit and the commission is \$50. In February the same customer buys 3 units. The first two units would be \$100 (\$50 each), but the commission for the third unit would be \$75 (\$75 each). The total commission for that month would be \$175. In March the customer buys 3 more units. The commission on the first two units would be \$150 (\$75 each), and the third unit be \$100. The total commission for March would be \$250.

Thank you to anyone that can help!!

#### Attachments

• 37.7 KB Views: 11

#### Nebu

##### Excel Ninja
Hi:

Using Power Query, it is easily achievable, find the attached.

Thanks

#### Attachments

• 255.1 KB Views: 6

#### Theprofessorlg

##### New Member
Thank you! I appreciate it!

#### Peter Bartholomew

##### Well-Known Member
To use a formula, first build a table of cumulative commission versus cumulative campaigns (either by formula or mental arithmetic). Then use INDEX to look up the cumulative commission at the end of each month. Subtracting the value at the beginning of the month from that achieved by the end of the month gives the commission earnt for the month.

Last edited:

#### Mamdouh Elfors

##### Member
Please . is it possible to solve formulas?

#### Peter Bartholomew

##### Well-Known Member
The attached shows it to be possible, so the answer to your last questions is 'Yes'.

#### Attachments

• 18 KB Views: 6

#### Mamdouh Elfors

##### Member
The attached shows it to be possible, so the answer to your last questions is 'Yes'.
Thank you very much