Will a SumProduct work with commission tiers

[SpudNix]

Hi there

Below is a commission tiers based on sales. So if someone makes $550k in sales, the commission will be $135k as per below. That calculation is very manual and I was wondering if a SumProduct function would work here.

Excel Workbook
	A	B	C	D	E	F	G
1	Sales Tiers		Commission %
2	$0	$250,000	0.3
3	$250,001	$500,000	0.21
4	$500,001	$1,000,000	0.15
5	$1,000,000	$3,000,000	0.06
6
7
8
9
10	So if someone has $550k in sales, the commission is $135k based on the above tiers
11	Sales	Commission
12	$250,000	$75,000
13	$250,000	$52,500
14	$50,000	$7,500
15	$550,000	$135,000
Sheet1 (2)

 

[RoryA]

Check Aladin's post 11 in this thread: https://www.mrexcel.com/forum/excel-questions/357834-tiered-commission-formula.html

 

[Dave Patton]

Excel 2010
	A	B	C
1	Sales Tiers	Commission	Differential
2	$0	0.3	0.3
3	$250,000	0.21	-0.09
4	$500,000	0.15	-0.06
5	$1,000,000	0.06	-0.09
6
7
8	Sales	Commission
9	550,000.00	135,000.00
10
11
12	T10_1709b	2c
13
2c

Cell Formulas
Range	Formula
C2	=B2-N(B1)
B9	= SUMPRODUCT(--(A9>$A$2:$A$5),A9-$A$2:$A$5,$C$2:$C$5)