SUMPRODUCT on two sets of ranges to calculate accumulated sum values

[Capolavoro009]

Apologies for garbled image below but what I’m trying to do is to determine which range my value falls between 0-60,703,125 and so on and then return corresponding commission value on the table range on the right hand side between 0-607,031

In addition as my value falls in different tiers (slices) I want to apply the corresponding commission for preceding steps plus the delta step.

For example:
If my stock value is 80,000,000 then the first 60,703,125 should be 607031 while 80million-60,7 million should be falling in the slice between 607031 and 1214062.

Thank you in advance for the help

Stock Value Tiers	Rev Share %age	Paid Commissions				60703125
	10%	R 0.00	R 60,703,125.00	10%	0	R 607,031.25
	15%	R 60,703,125.00	R 91,054,687.50	20%	R 607,031.25	R 1,214,062.50
	20%	R 91,054,687.50	R 121,406,250.00	30%	R 1,214,062.50	R 1,821,093.75
	60%	R 121,406,250.00	R 364,218,750.00	40%	R 1,821,093.75	R 2,428,125.00
	100%	R 364,218,750.00	R 607,031,250.00	60%	R 2,428,125.00	R 3,642,187.50

 

[Eric W]

Welcome to the MrExcel forum!

I had a little trouble figuring out your tables, but I think I got the gist. I rearranged your table slightly, but you should be able to adapt as needed:

Book1
	A	B	C	D	E	F	G
1	Stock Value		Tier Minimum	Tier Maximum	Payout Rate	Delta	Commission
2	80000000		$ -	$ 60,703,125.00	10.00%	10.00%	$ 6,070,312.50
3			$ 60,703,125.00	$ 121,406,250.00	20.00%	10.00%	$ 3,859,375.00
4			$ 121,406,250.00	$ 182,109,375.00	30.00%	10.00%	$ -
5			$ 182,109,375.00	$ 242,812,500.00	40.00%	10.00%	$ -
6			$ 242,812,500.00	$ 364,218,750.00	60.00%	20.00%	$ -
7
8						$9,929,687.50	$ 9,929,687.50
Sheet1

Cell Formulas
Range	Formula
F2:F6	F2	=E2-N(E1)
G2:G6	G2	=MEDIAN(0,D2-C2,A$2-C2)*E2
F8	F8	=SUMPRODUCT((A2-C2:C6),F2:F6,--(A2>=C2:C6))
G8	G8	=SUM(G2:G6)

A2 has your stock value, C:D has your tier ranges, low to high, E has the payout rate for that tier. At this point you have 2 options. In column F is a simple formula that just calculates the difference between the payout rates. Then the F8 formula uses the C:F ranges to calculate the total payout. Or in column G there is a formula that calculates the amount for the tier on that row. Then the G8 formula just sums them up. It's a nice check that they match.

So you can use one of those options. I'm not sure what you want to do with your original table on the left, but you can do the same thing with that, or choose the table you want to use based on some parameter.

 

[Dave Patton]

a little more concise

Commission Cumulative Sales.xlsm
	A	B
12	80,000,000	9,929,687.50
13
14	Tier Minimum	Payout Rate
15
16	0	0.1
17	60,703,125	0.2
18	121,406,250	0.3
19	182,109,375	0.4
20	242,812,500	0.6
21
1e

Cell Formulas
Range	Formula
B12	B12	=SUM((A12>A16:A20)*(A12-A16:A20)*(B16:B20-B15:B19))

 

[Capolavoro009]

Thank you very much but it seems like every time i need to input a different stock value individually to calculate commissions, is that right?

is there a way to calculate commissions based on following?:

Stock Value		Tier Min	Tier Max	Payout Rate	Delta	Commission
60,703,125		-	60,703,125	10%	10%	6,070,313
91,054,688		60,703,125	91,054,688	20%	10%	-
303,515,625		91,054,688	121,406,250	30%	10%
		121,406,250	364,218,750	40%	10%
		364,218,750	607,031,250	60%	20%

 

[Capolavoro009]

BAsically this is the original table:

Stock Value	Stock Value Tiers	Paid Commissions
10%	ZAR -	ZAR 60,703,125	0	ZAR 607,031
15%	ZAR 60,703,125	ZAR 91,054,688	ZAR 607,031	ZAR 1,214,063
20%	ZAR 91,054,688	ZAR 121,406,250	ZAR 1,214,063	ZAR 1,821,094
60%	ZAR 121,406,250	ZAR 364,218,750	ZAR 1,821,094	ZAR 2,428,125
100%	ZAR 364,218,750	ZAR 607,031,250	ZAR 2,428,125	ZAR 3,642,188

So if the stock falls between 91,054,688 & 121,406,250, first 60,703,125 should get a commission of 607,031 while the balance should get a difference of 1,214,063 and 607,031, whatever that number is.

I hope this is clear and apologies for back and forth...

 

[Dave Patton]

If you are using an old version of Excel and/or if you prefer to use SumProduct, the same logic applies. =SUMPRODUCT(--(A2>aB),(A2-aB),aR)
What results do you expect for the values shown in A13:A15
Did you try the formula suggested?
You can name the bracket information and the rate differential information

Commission Cumulative Sales.xlsm
	A	B	C
1
2	80,000,000	9,929,687.50	9,929,687.50
3
4	Tier Minimum	Payout Rate
5
6	0	10%
7	60,703,125	20%
8	91,054,688	30%
9	121,406,250	40%
10	364,218,750	60%
11
12
13	80,000,000	9,929,687.50
14	200,000,000	52,683,593.70
15	400,000,000	139,839,843.70
1e

Cell Formulas
Range	Formula
B2	B2	=SUM((A2>$A$6:$A$10)*(A2-$A$6:$A$10)*($B$6:$B$10-$B$5:$B$9))
C2	C2	=SUM((A2>aB)*(A2-aB)*aR)
B13:B15	B13	=SUM((A13>aB)*(A13-aB)*aR)

 

[Capolavoro009]

If you are using an old version of Excel and/or if you prefer to use SumProduct, the same logic applies. =SUMPRODUCT(--(A2>aB),(A2-aB),aR)
What results do you expect for the values shown in A13:A15
Did you try the formula suggested?
You can name the bracket information and the rate differential information

Commission Cumulative Sales.xlsm
	A	B	C
1
2	80,000,000	9,929,687.50	9,929,687.50
3
4	Tier Minimum	Payout Rate
5
6	0	10%
7	60,703,125	20%
8	91,054,688	30%
9	121,406,250	40%
10	364,218,750	60%
11
12
13	80,000,000	9,929,687.50
14	200,000,000	52,683,593.70
15	400,000,000	139,839,843.70
1e

Cell Formulas
Range	Formula
B2	B2	=SUM((A2>$A$6:$A$10)*(A2-$A$6:$A$10)*($B$6:$B$10-$B$5:$B$9))
C2	C2	=SUM((A2>aB)*(A2-aB)*aR)
B13:B15	B13	=SUM((A13>aB)*(A13-aB)*aR)

Hi Dave,

The issue is that the stock value sits in one range however the commission value sits in the other and the target is to check which tier/slab the stock value sits and then calculate the commission payable from the other tier. The %age values literally become irrelevant, at least from what i am trying to achieve.

Stock Value Tiers	Paid Commissions
ZAR -	ZAR 60,703,125	0	ZAR 607,031
ZAR 60,703,125	ZAR 91,054,688	ZAR 607,031	ZAR 1,214,063
ZAR 91,054,688	ZAR 121,406,250	ZAR 1,214,063	ZAR 1,821,094
ZAR 121,406,250	ZAR 364,218,750	ZAR 1,821,094	ZAR 2,428,125
ZAR 364,218,750	ZAR 607,031,250	ZAR 2,428,125	ZAR 3,642,188

 

[Dave Patton]

Are you trying to calculate the commission or are you trying to fill blanks in a form?
What results do you require from the values cited?

 

[Capolavoro009]

I will have stock values according to the ranges above. I want to calculate and populate the commissions based on the tier

 

[Capolavoro009]

I will have stock values according to the ranges above. I want to calculate and populate the commissions based on the tier

for example:

if the stock value is ZAR 91,054,688, for the first ZAR 60,703,125, the calculated commission should be ZAR 607,031 PLUS the "remaining" (91,054,688-60,703,125) should be calculated automatically between ZAR 607,031 & ZAR 1,214,063 and so on..