Sales commission formula

**shinymagic** · 10-30-2009, 08:56 AM

hi, need help in generating formula with this sales amount $457986.75 when

Commission of 2% is payable for the 1st $100,000 of sales.
above $100,000 and up to $300,000, a 3% commission is payable.
above $300,000 will get a commission of 4%.

Amount $457 986.75

Comission rate:
1st $100 000 = 2%
$100 000 - $300 000 = 3%
> $300 000 = 4%

**DonkeyOte** · 10-30-2009, 09:00 AM

You can use SUMPRODUCT for this

=SUMPRODUCT(--(sales>{0,100000,300000}),sales-{0,100000,300000},{0.02,0.01,0.01})

where sales is your sales amount - be it cell reference (to value) or constant.

(output of the above based on your values would be 14,319.47)

**daddylonglegs** · 10-30-2009, 09:02 AM

Here's one option

=A1*4%-MIN(300000,A1)*(4%-3%)-MIN(100000,A1)*(3%-2%)

where Sales amount is in A1

**shinymagic** · 10-30-2009, 10:15 AM

awesome!! it's works! thank you for your replies and formula appreicate it!

But what if i'm computing for salesman commission? do i use IF funtion or VLOOKUP to calculate salesman commission amount?

Salesman A: $457 986

Comission rate:
1st $100 000 = 2%
$100 000 - $300 000 = 3%
> $300 000 = 4%

**DonkeyOte** · 10-30-2009, 10:22 AM

I'm afraid I don't understand your question. The formulae provided calculate commission on a tiered basis for a given sales amount. You say it works but then ask the same question... perhaps provide an example of what it is you actually require.

If you're saying that the commission bands & % rates fluctuate pending salesperson then yes you can do that but we would need more info in terms of how/where you store this info. - a sample file would be a good idea.

**grace43** · 11-17-2009, 12:06 AM

Hi,

I am desperately trying to do a similar thing. My boss just gave me this spreadsheet and wants it done pronto.

I need to be able to enter the commission tier amounts as cells, not numbers, in case they change. Here is what I need:

Base salary paid
Up to 2.5 times base salary made in sales- 3%
Another 100000 on top of that is 20%
anything over that is 35%

So en example:
Base Salary 50000
up to 125000 earns 3% (3750)
125001 to 225000 earns 20% (20000)
over 225001 earns 35% (8750)
So commission earnt is $32,500 on top of $50,000.

How do I create a formula so that my boss can enter a different base salary and hypothetical sales amount and calculate the commission earnt?

PLEASE HELP! I will be forever grateful!!!