Help with SUMPRODUCT/Array Formula

[DefiantEgg90]

Hi,

I am building a spreadsheet that calculates tiered commissions for salespeople at my company. So far I have built my sheet so that it looks at each commission tier in a separate row and calculates the appropriate amount for that tier. Then, a simple sum at the bottom calculates the total commission.

What I would like to do is figure out how to write a formula to get the total in a single formula, rather than calculate each line and then summing (this will make it a lot easier to scale my model for many salespeople and many transactions), but I can't figure out how to do it. I think it's some combination of SUMPRODUCT and/or an array formula. Could someone help? The yellow cell in F11 is what I would like to achieve in a single formula without all the precedent calculations in F7:10.

Commission-MrExcel.xlsx
	A	B	C	D	E	F
1	Qualified Revenue	50,000
2	Cumulative Revenue	430,000
3	Prior Cumulative Revenue	380,000
4
5			Commission Scale
6			Min	Max	Rate	Commission
7			-	140,625	0%	-
8			140,625	400,000	35%	7,000.00
9			400,000	600,000	40%	12,000.00
10			600,000	999,999,999	45%	-
11						19,000.00
Sheet1

Cell Formulas
Range	Formula
B3	B3	=B2-B1
F7:F10	F7	=MAX(MIN(CumRev,D7)-MAX(PriorCumRev,C7),0)*E7
F11	F11	=SUM(F7:F10)

Named Ranges
Name	Refers To	Cells
CumRev	=Sheet1!$B$2	B3, F7:F10
PriorCumRev	=Sheet1!$B$3	F7:F10
QualRev	=Sheet1!$B$1	B3

 

[Dave Patton]

Commission2022.xlsm
	A	B	C	D
1	Qualified Revenue	50,000.00
2	CumRev	430,000.00
3	Prior Cumulative Revenue	380,000.00
4		19,000.00	19,000.00
5			Commission Scale	Rate
6			Min
7			0.00	0
8			140,625.00	0.35
9			400,000.00	0.4
10			600,000.00	0.45
11
3b

Cell Formulas
Range	Formula
B3	B3	=B2-B1
B4	B4	=SUMPRODUCT(--(B2>C7:C10),B2-C7:C10,D7:D10-D6:D9)-SUMPRODUCT(--(B3>C7:C10),B3-C7:C10,D7:D10-D6:D9)
C4	C4	=SUMPRODUCT(--(B2>aB),B2-aB,aR)-SUMPRODUCT(--(B3>aB),B3-aB,aR)

With the formula in C4, the Bracket Array is named aB and Rate Differential Array is named aR.

Review the formula in B4 for the full information on the particular arrays; select the range like c7:c10 in the formula and press F9.

If you already have the calculation for the previous cumulative amount, you could just subtract that amount.

 

[Dave Patton]

With Excel 365 you can use Sum.

 

[DefiantEgg90]

Commission2022.xlsm
	A	B	C	D
1	Qualified Revenue	50,000.00
2	CumRev	430,000.00
3	Prior Cumulative Revenue	380,000.00
4		19,000.00	19,000.00
5			Commission Scale	Rate
6			Min
7			0.00	0
8			140,625.00	0.35
9			400,000.00	0.4
10			600,000.00	0.45
11
3b

Cell Formulas
Range	Formula
B3	B3	=B2-B1
B4	B4	=SUMPRODUCT(--(B2>C7:C10),B2-C7:C10,D7:D10-D6:D9)-SUMPRODUCT(--(B3>C7:C10),B3-C7:C10,D7:D10-D6:D9)
C4	C4	=SUMPRODUCT(--(B2>aB),B2-aB,aR)-SUMPRODUCT(--(B3>aB),B3-aB,aR)

With the formula in C4, the Bracket Array is named aB and Rate Differential Array is named aR.

Review the formula in B4 for the full information on the particular arrays; select the range like c7:c10 in the formula and press F9.

If you already have the calculation for the previous cumulative amount, you could just subtract that amount.

Beautiful, exactly what I was looking for. I'm not terribly familiar with the "--" syntax in SUMPRODUCT, other than I know it evaluates a statement to a Boolean value (I think). I'll have to read up on that. Thanks for the help!

 

[DefiantEgg90]

With Excel 365 you can use Sum.

Unfortunately, my company has not progressed beyond Excel 2016 as of now

 

[Dave Patton]

The double negative -- coerces True and False to 1 and 0 Which expedites some formulas. Arithmetic operations also coerce True and False to 1 and 0.
Try the 2 parts of the formula in distinct cells and then review the logic with Formulas Evaluate Formula.

 

[Dave Patton]

Commission2022.xlsm
	A	B	C	D	E	F	G	H	I	J
1								on cumulative revenue
2		Jan	Feb	Mar	Apr	May	Jun	Jul		Total
3	Sales	120,000.00	150,000.00	110,000.00	50,000.00					430,000.00
4
5	Commission	0.00	45,281.25	38,500.00	19,000.00	0.00	0.00	0.00		102,781.25
6
7	Total									102,781.25
8
3b

Cell Formulas
Range	Formula
J3,J5	J3	=SUM(B3:H3)
B5:H5	B5	=SUMPRODUCT(--(SUM($B$3:B3)>aB),SUM($B$3:B3)-aB,aR)-SUM($A$5:A5)
J7	J7	=SUMPRODUCT(--(J3>aB),J3-aB,aR)

Try reviewing the formula with Formulas Evaluate Formula
I did not know this site would show the following when I posted the address; I hope that it works OK. see Excel Magic Trick 673

MrExcel posted a YouTube Video on this see

see Excel Magic Trick 673