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]

Did you review the manual calculation that was included in the post?
Row 2 used 65000 of the first tier which leaves 25000.
Row 3 215000. 25000 is at .3 = 7500 and 190000 @ .2 38000 Total 45500.

The Tiers and Categories cited are used.

 

[CA_Punit]

I suppose you should try with different data set before you implement it.

 

[mhlester]

I suppose you should try with different data set before you implement it.

Tested with the actual data through 243 rows of commissions totaling 496,146.26, with the cumulative LLC share totaling 147,076.15. I added another row of 20,000 commission to make sure the cumulative LLC share would exceed $150,000. The results using your formula, 4, 974.62,are identical to the results using my clunky formula.

 

[CA_Punit]

So our effort paid well. Thanks for checking..

 

[mhlester]

So our effort paid well. Thanks for checking..

I am very grateful to you and Dave for your patient, tireless, and successful efforts in solving my problem! I marked your answer as solved (I think)!

Thank you very much, CA_Punit and Dave Patton for sharing your expertise!