SUMPRODUCT, Tiered Commissions, One for Old Customers, One for New Customers

[mhlester]

I am sorry if this question has been answered in the thread, Multiple Tiered Calculations, or another thread, but they are so complex, it is difficult for me to follow to know if they will help me.

It seems I have a comparatively simple calculation, but it's too hard for me with my limited knowledge of advanced Excel functions.

The Company keeps a percentage of each sale depending on the category (pre-existing customer or new customer), and depending on the Company's cumulative share of commissions year-to-date.

I have manually calculated what I think are the correct amounts in the mini-table, based on the tiered commission structure shown in the mini-sheet and the two categories, 1 and 2.

But I think there might be a SUMPRODUCT, or other EXCEL function, to calculate the correct commission for each sale, so I have created a column where the formula might go.

Thank you for taking a look and hopefully providing a solution.

Best wishes,
Michael

SUMPRODUCT.xlsx
	A	B	C	D	E	F
1	Commission	Company Share of Commission Manually Calculated	Cumulative	Formula?	Category
2	65,000.00	26,000.00	26,000.00	SUMPRODUCT?	2	.4*65000
3	60,000.00	18,000.00	44,000.00	SUMPRODUCT?	1	.3*60000
4	75,000.00	30,000.00	74,000.00	SUMPRODUCT?	2	.4*75000
5	85,000.00	22,333.33	96,333.33	SUMPRODUCT?	1	16000/0.3=53333.33*.3 + 31666.67*.2
6	80,000.00	24,000.00	120,333.33	SUMPRODUCT?	2	.3*80000
7	100,000.00	29,889.09	150,222.42	SUMPRODUCT?	2	29666.67/.3=99998.9*.3 + 1111.1*.2
8
9
10	Tier Start	Tier End	Category 1	Category 2
11	0	90000	30%	40%
12	90000	150000	20%	30%
13	150000		20%	20%
Sheet1

Cell Formulas
Range	Formula
B2,B4	B2	=A2*$D$11
C2:C7	C2	=SUM($B$2:B2)
B3	B3	=A3*$C$11
B5	B5	=16000+$C$12*(A5-(16000/0.3))
B6	B6	=A6*$D$12
B7	B7	=29666.67/0.3*$D$12+$D$13*(A7-(29666.37/0.3))

 

[Dave Patton]

The formula can be completed with or without the Cumulative column and it can be completed
without using SumProduct, Vlookup, or the Table of rates. The result yields the same result as I posted previously;
consequently, I did not post the example.

You stated "One of the salespersons has 8 tiers!",. It would be interesting to see the formula that you use for that calculation.

 

[CA_Punit]

Hi

Try this

Book11.xlsx
	A	B	C	D
1	Total Sales	Category	0
2	65,000.00	2.00	26,000.00	26,000.00
3	2,15,000.00	1.00	90,333.33	64,333.33
4	75,000.00	2.00	1,12,833.33	22,500.00
5	85,000.00	1.00	1,29,833.33	17,000.00
6	80,000.00	2.00	1,52,555.56	22,722.22
7	1,00,000.00	2.00	1,72,555.56	20,000.00
8
9
10	Slab	Category 1	Category 2
11	1	0.3	0.4
12	90001	0.2	0.3
13	150001	0.2	0.2
14	900001	0.2	0.2
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=SUM($D$1:D2)
D2:D7	D2	=SUM(IFERROR(LET(a,(($A$12:$A$14-$A$11:$A$13)-IF(($A$12:$A$14-$A$11:$A$13)+IF((C1-($A$12:$A$14-1))>0,0,(C1-($A$12:$A$14-1)))<0,0,($A$12:$A$14-$A$11:$A$13)+IF((C1-($A$12:$A$14-1))>0,0,(C1-($A$12:$A$14-1)))))/CHOOSE(B2,$B$11:$B$13,$C$11:$C$13),b,IFERROR(MATCH(A2,a,1),0)+1,c,IFERROR(MATCH(A2,a,1),0),d,INDEX(a,SEQUENCE(b),1),e,IFERROR(INDEX(a,SEQUENCE(c),1),0),f,INDEX(CHOOSE(B2,$B$11:$B$13,$C$11:$C$13),SEQUENCE(b)),IF(A2>d,d,A2-SUM(e))*f),0))

There is one drawback which i am trying to figure out as to how to solve it
So if your 1st total sales is beyond the First slab there is probelm with the formula
so if 1st sales is

Book11.xlsx
	A	B	C	D
1	Total Sales	Category	0
2	2,15,000.00	2.00	-66,000.00	-66,000.00
3	1.00	1.00	-65,999.70	0.30
4	75,000.00	2.00	-35,999.70	30,000.00
5	85,000.00	1.00	-10,499.70	25,500.00
6	80,000.00	2.00	21,500.30	32,000.00
7	1,00,000.00	2.00	61,500.30	40,000.00
8
9
10	Slab	Category 1	Category 2
11	1	0.3	0.4
12	90001	0.2	0.3
13	150001	0.2	0.2
14	900001	0.2	0.2
Sheet1

Cell Formulas
Range	Formula
C2:C7	C2	=SUM($D$1:D2)
D2:D7	D2	=SUM(IFERROR(LET(a,(($A$12:$A$14-$A$11:$A$13)-IF(($A$12:$A$14-$A$11:$A$13)+IF((C1-($A$12:$A$14-1))>0,0,(C1-($A$12:$A$14-1)))<0,0,($A$12:$A$14-$A$11:$A$13)+IF((C1-($A$12:$A$14-1))>0,0,(C1-($A$12:$A$14-1)))))/CHOOSE(B2,$B$11:$B$13,$C$11:$C$13),b,IFERROR(MATCH(A2,a,1),0)+1,c,IFERROR(MATCH(A2,a,1),0),d,INDEX(a,SEQUENCE(b),1),e,IFERROR(INDEX(a,SEQUENCE(c),1),0),f,INDEX(CHOOSE(B2,$B$11:$B$13,$C$11:$C$13),SEQUENCE(b)),IF(A2>d,d,A2-SUM(e))*f),0))

it is giving error.

So please check and let me know whats wrong the other limitation of the formula

 

[CA_Punit]

One Error which i came to know that the Total Sales Should not be greater than 200000. If it is so than it will show up error.

 

[mhlester]

Hi, CA_Punit!

Thank you for joining in!

If your question directed at me, I cannot answer it. My formula does work if the first commission is 215,000, but the formula in your post above is beyond my understanding.

Hi, Dave,

The following spreadsheet is just a straightforward example for one of the salespeople with several tiers, but no categories to worry about, and that is based on gross commissions, not on the Company Share, that works with SUMPRODUCT...

I haven't actually verified the calculations in a long time, but we've been using it for years for this salesperson, and I am sure I verified it in the past.

SUMPRODUCT.xlsx
	A	B	C	D	E	F
1	Gross Comm	Cumulative Gross	LLC Share		Amount	LLC%
2	35,687.55	35,687.55	28,550.04		-	0
3	150,000.00	185,687.55	118,862.49		-	0.8
4	97,777.89	283,465.44	46,973.65		180,000.00	0.6
5	55,000.00	338,465.44	20,653.46		200,000.00	0.5
6					250,000.00	0.4
7					325,000.00	0.3
8					1,000,000.00	0.3
Sheet3

Cell Formulas
Range	Formula
B2:B5	B2	=SUM($A$2:A2)
C2:C5	C2	=SUMPRODUCT(--(SUM(A$2:A2)>=$E$3:$E$8),SUM(A$2:A2)-$E$3:$E$8,$F$3:$F$8-$F$2:$F$7)-SUM($C$1:C1)

 

[Dave Patton]

You did not reply when I inquired if you wanted a simple formula that did not include SumProduct or Lookup.

Your formula yields the correct result.
You can consider the following:
1. You calculate the cumulative amount in Column B, you could use those numbers in the formula.
2. The last tier is not required; 1000000 has the same rate as the 325000.
3. I named the array information; the array of
Brackets aB is equivalent to D3:D7 ={0;180000;200000;250000;325000}
and aR is equivalent to the array of rate differentials ={0.8;-0.2;-0.1;-0.1;-0.1}

Commission2020.xlsm
	A	B	C
1	Gross Comm	Cumulative Gross	LLC Share
2	35,687.55	35,687.55	28,550.04
3	150,000.00	185,687.55	118,862.49
4	97,777.89	283,465.44	46,973.65
5	55,000.00	338,465.44	20,653.46
6			215,039.63
7		215,039.63
2a

Cell Formulas
Range	Formula
B2:B5	B2	=A2+N(B1)
C2	C2	=SUMPRODUCT(--(B2>=aB),B2-aB,aR)-SUM($C$1:C1)
C3:C5	C3	=SUMPRODUCT(--(B3>=$I$3:$I$7),B3-$I$3:$I$7,$J$3:$J$7-$J$2:$J$6)-SUM($C$1:C2)
C6	C6	=SUM(C2:C5)
B7	B7	=SUMPRODUCT(--(B5>=aB),B5-aB,aR)

 

[Dave Patton]

For 620,000 the maximum would be 148000 100% Category 2 and Minimum 100% Category 1 133,000.

Commission2020.xlsm
	A	B	C	D	E
1	Total Sales	Category		Manual Check
2	65,000	2	26,000.00	26,000.00	65000 of tier 1 used
3	215,000	1	45,500.00	45,500.00	25000 of tier 1 balance @ 2%
4	75,000	2	15,000.00	15,000.00
5	85,000	1	17,000.00	17,000.00
6	80,000	2	16,000.00	16,000.00
7	100,000	2	20,000.00	20,000.00
8	620,000		139,500.00	139,500.00
1ddd

Cell Formulas
Range	Formula
C2:C7	C2	=A2*0.2+(MAX(0,MIN(A2,90000-SUM($A$1:A1)))+MAX(0,MIN(A2,150000-SUM($A$1:A1)))*(B2=2))*0.1
D2	D2	=A2*0.4
D3	D3	=(90000-A2)*0.3+(A3-25000)*0.2
D4:D7	D4	=A4*0.2
C8:D8	C8	=SUM(C1:C7)
A8	A8	=SUM(A2:A7)

 

[CA_Punit]

Try this. I think this will solve your probelm. Try with different data.

Book11.xlsx
	A	B	C	D
1	Total Sales	Category	0	Company Sales
2	65,000.00	2.00	26,000.00	26,000.00
3	60,000.00	1.00	44,000.00	18,000.00
4	75,000.00	2.00	74,000.00	30,000.00
5	85,000.00	1.00	96,333.33	22,333.33
6	80,000.00	2.00	1,20,333.33	24,000.00
7	1,00,000.00	2.00	1,50,222.22	29,888.89
8
9
10	Slab	Category 1	Category 2
11	1	0.3	0.4
12	90001	0.2	0.3
13	150001	0.2	0.2
14	450001	0.15	0.15
15	900001	0.15	0.15
Sheet1 (2)

Cell Formulas
Range	Formula
C2:C7	C2	=SUM($D$1:D2)
D2:D7	D2	=SUM(IFERROR(LET(a,(($A$12:$A$15-$A$11:$A$14)-IF(($A$12:$A$15-$A$11:$A$14)+IF((C1-($A$12:$A$15-1))>=0,0,(C1-($A$12:$A$15-1)))<0,0,($A$12:$A$15-$A$11:$A$14)+IF((C1-($A$12:$A$15-1))>0,0,(C1-($A$12:$A$15-1)))))/CHOOSE(B2,$B$11:$B$14,$C$11:$C$14),b,IFERROR(MATCH(A2,a+1,1),0)+1,c,IFERROR(MATCH(A2,a+1,1),0),d,INDEX(a,SEQUENCE(b),1),e,IFERROR(INDEX(a,SEQUENCE(c),1),0),f,INDEX(CHOOSE(B2,$B$11:$B$14,$C$11:$C$14),SEQUENCE(b)),IF(A2>d,d,A2-SUM(e))*f),0))

 

[mhlester]

Hi, Dave,

Yes, I would want a simple formula that does not include Lookup or SumProduct! if it gives the correct result.

Your row 3 for $215,000 in the chart above does not give the same result as I get (you get 45,500, I get 64,333.33), so there must be some disconnect between the logic of your calculation and the logic of mine. I know what the deal is, so I know what the correct result should be.

Row 3 is in Category 1. Until the Company share of $215,000 brings it cumulative share (of both Category 1 and Category 2 commissions combined) to $90,000, the Company share is 30%. If we apply 30% to the entire $215,000 commission we get. $64,500. If we add that to $26,000, we get $90,500. So most of the Company Share of the $215,000 commission is payable at 30%. only a very small portion is at 20%. Thus, $64,333.33 appears to be the correct Company Share. Your formula for some reason computes the Company Share mostly at 20% and a small portion at 30%. This is not how the Company Share is calculated.

SUMPRODUCT.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Commission	LLC Share	LLC Cumulative	Adam Share	Adam Cumulative	Category
2	-
3	65,000.00	26,000.00	26,000.00	39,000.00	39,000.00	2
4	215,000.00	64,333.33	90,333.33	150,666.67	189,666.67	1
5	75,000.00	22,500.00	112,833.33	52,500.00	242,166.67	2
6	85,000.00	17,000.00	129,833.33	68,000.00	310,166.67	1
7	80,000.00	22,722.22	152,555.56	57,277.78	367,444.44	2
8	100,000.00	20,000.00	172,555.56	80,000.00	447,444.44	2
9	100,000.00	20,000.00	192,555.56	80,000.00	527,444.44	2
10	50,000.00	10,000.00	202,555.56	40,000.00	567,444.44	1
11	770,000.00	202,555.56		567,444.44
12
13
14	2 Below 90	2 Above 150	1 Below 90	1 Above 150	2 Straddle 90	1 Straddle 90	2 Straddle 150	1 Straddle 150	2 Between 90 & 150	1 Between 90 & 150	Totals
15											-
16	26,000.00	-	-	-	-	-	-	-	-	-	26,000.00
17	-	-	-	-	-	64,333.33	-	-	-	-	64,333.33
18	-	-	-	-	-	-	-	-	22,500.00	-	22,500.00
19	-	-	-	-	-	-	-	-	-	17,000.00	17,000.00
20	-	-	-	-	-	-	22,722.22	-	-	-	22,722.22
21	-	20,000.00	-	-	-	-	-	-	-	-	20,000.00
22	-	20,000.00	-	-	-	-	-	-	-	-	20,000.00
23	-	-	-	10,000.00	-	-	-	-	-	-	10,000.00
24	-	-	-	-	-	-	-	-	-	-	-
25
26
27				Tier Start	Tier End	Category 1	Category 2
28				0	90000	30%	40%
29				90000	150000	20%	30%
30				150000		20%	20%
Sheet2

Cell Formulas
Range	Formula
B3:B10	B3	=K16
C3:C10	C3	=SUM($B$3:B3)
E3:E10	E3	=SUM($D$3:D3)
A11:B11,D11	A11	=SUM(A2:A10)
D3:D10	D3	=A3-B3
K15	K15	=SUM(A15:H15)
A16:A24	A16	=IF(F3<>2,0,IF(C3<$E$28,A3*$G$28,0))
B16:B24	B16	=IF(F3<>2,0,IF(C2>$E$29,A3*$G$30,0))
C16:C24	C16	=IF(F3<>1,0,IF(C3<$E$28,A3*$F$28,0))
D16:D24	D16	=IF(F3<>1,0,IF(C2>$E$29,A3*$F$30,0))
E16:E24	E16	=IF(F3<>2,0,IF(AND(C2<$E$28,C2+A3*$G$28>$E$28,C2+A3*$G$28<$E$29),($E$28-C2)+(A3-(($E$28-C2)/$G$28))*$G$29,0))
F16:F24	F16	=IF(F3<>1,0,IF(AND(C2<$E$28,C2+A3*$F$28>$E$28,C2+A3*$F$28<$E$29),($E$28-C2)+(A3-(($E$28-C2)/$F$28))*$F$29,0))
G16:G24	G16	=IF(F3<>2,0,IF(AND(C2<$E$29,C2+A3*$G$29>$E$29,C2+A3*$G$29>$E$29),($E$29-C2)+(A3-(($E$29-C2)/$G$29))*$G$30,0))
H16:H24	H16	=IF(F3<>1,0,IF(AND(C2<$E$29,C2+A3*$F$29>$E$29,C2+A3*$F$29>$E$30),($E$29-C2)+(A3-(($E$29-C2)/$F$29))*$F$30,0))
I16:I24	I16	=IF(F3<>2,0,IF(AND(C2>$E$28,C2+(A3*$G$29)<$E$29),A3*$G$29,0))
J16:J24	J16	=IF(F3<>1,0,IF(AND(C2>$E$28,C2+(A3*$F$29)<$E$29),A3*$F$29,0))
K16:K24	K16	=SUM(A16:J16)

 

[CA_Punit]

Try from post 27.

 

[mhlester]

Try this. I think this will solve your probelm. Try with different data.

Book11.xlsx
	A	B	C	D
1	Total Sales	Category	0	Company Sales
2	65,000.00	2.00	26,000.00	26,000.00
3	60,000.00	1.00	44,000.00	18,000.00
4	75,000.00	2.00	74,000.00	30,000.00
5	85,000.00	1.00	96,333.33	22,333.33
6	80,000.00	2.00	1,20,333.33	24,000.00
7	1,00,000.00	2.00	1,50,222.22	29,888.89
8
9
10	Slab	Category 1	Category 2
11	1	0.3	0.4
12	90001	0.2	0.3
13	150001	0.2	0.2
14	450001	0.15	0.15
15	900001	0.15	0.15
Sheet1 (2)

Cell Formulas
Range	Formula
C2:C7	C2	=SUM($D$1:D2)
D2:D7	D2	=SUM(IFERROR(LET(a,(($A$12:$A$15-$A$11:$A$14)-IF(($A$12:$A$15-$A$11:$A$14)+IF((C1-($A$12:$A$15-1))>=0,0,(C1-($A$12:$A$15-1)))<0,0,($A$12:$A$15-$A$11:$A$14)+IF((C1-($A$12:$A$15-1))>0,0,(C1-($A$12:$A$15-1)))))/CHOOSE(B2,$B$11:$B$14,$C$11:$C$14),b,IFERROR(MATCH(A2,a+1,1),0)+1,c,IFERROR(MATCH(A2,a+1,1),0),d,INDEX(a,SEQUENCE(b),1),e,IFERROR(INDEX(a,SEQUENCE(c),1),0),f,INDEX(CHOOSE(B2,$B$11:$B$14,$C$11:$C$14),SEQUENCE(b)),IF(A2>d,d,A2-SUM(e))*f),0))

Yes, indeed, it does! Thank you very much, CA_Punit! Now, I have to study your formula and learn how to do it for the next crazy deal my client makes!