One way would be to have a cell that contains the Total amount invoiced,
and one that contains the commissions paid to date. Then you could use:

=ROUND(SUMPRODUCT(--(TotalInvoiced{0,25000,50000,75000,150000,250000,
500000}), (TotalInvoiced-{0,25000,50000,75000,150000,250000,500000}),
{0.07,0.01,0.01,0.01,0.01,0.01,0.03}),2) - PreviousCommission

Another way would be to use the Current invoice and retain the total
Previous-ly invoiced to give something like:

=ROUND(SUMPRODUCT(--((Current+Previous){0,25000,50000,75000,150000,
250000,500000}),((Current+Previous)-{0,25000,50000,75000,150000,
250000,500000}),{0.07,0.01,0.01,0.01,0.01,0.01,0.0 3}),2) -
ROUND(SUMPRODUCT(--(Previous{0,25000,50000,75000,150000,250000,50000 0}),
(Previous-{0,25000,50000,75000,150000,250000,500000}),{0.07, 0.01,0.01,
0.01,0.01,0.01,0.03}),2)

Other combinations apply, of course.

if you put your commission schedule in a table:

J K
1 0 7%
2 25000 1%
3 50000 1%
4 75000 1%
5 150000 1%
6 250000 1%
7 500000 3%

The first formula reduces to:

=ROUND(SUMPRODUCT(--(TotalInvoicedJ1:J7),(TotalInvoiced-J1:J7),
K1:K7),2) - PreviousCommission

and the second to:

=ROUND(SUMPRODUCT(--((Current+Previous)J1:J7),((Current+Previous)-J1:J7)
,K1:K7),2) - ROUND(SUMPRODUCT(--((Previous)J1:J7), ((Previous)-J1:J7)
,K1:K7),2)



In article ,
Amanda wrote:

Thanks Joeu

The website you gave me is very useful. My problem is that the commission
percentages depend on the cumulative total.

If the cumulative total was at 74,000 and the next inv is for 2,000. The
commission on this invoice needs to be worked out on 1,000 at 9% and 1,000
at 10%.

Hope you can be of some more help

" wrote:

Amanda wrote:
I have been trying to use SUMPRODUCT to work out commissions due on each
invoice total (in column L). The commission due on each invoice is worked
out on percentages based on a running total (in column M)

Commission Schedule
0-25k 7%
25k - 50k 8%
50k - 75k 9%
75k - 150k 10%
150k - 250k 11%
250k - 500k 12%
500k+ 15%

Typically, such commission schedules mean: 7% on the first 25k, 8% on
the next 25k, etc. So if the sale were 30k, the commission would be
2150 (7%*25k plus 8%*5k). Is that what you mean?

If so, then for a SUMPRODUCT solution, you might look at
http://www.mcgimpsey.com/excel/variablerate.html .

Column L Column M
Due Cumulative
1,171.15 ‚¬ 1,171.15
0.00 ‚¬ 1,171.15
0.00 ‚¬ 1,171.15
51,031.32 ‚¬ 52,202.47
[.... etc ....]
My problem is that my final answer works out the percentages correctly but
takes it from the cumalitive total and not the invoice amount.
Please can anyone help?

It would be a lot easier to help you if you posted your formula(s). If
the table contains the sale price in A1 and cumulative sales in A2, and
your formula is fundamentally correct, as you claim, but it is based on
cumulative sales, simply change the reference from A2 to A1 in the
formula.