Array Formula to Total based on % in Lookup

[RicardoCubed]

Help please.
Looking for an array formula I believe that will look at range and then calculate TOTAL of all Bonus base on a table. The right answer should be 984,000.
The array formula is from another sample that worked where the bonus was not based on a range.
Thanks for any help out there!

Excel 2010
	A	B	C	D	E
22	Bonus	4.60	984000 is the correct answer
23
24	Amount
25	100000		Sale Amt	%	Bonus
26	110000		0	0	0
27	120000		100000	10%	10000
28	130000		150000	20%	30000
29	140000		200000	30%	60000
30	150000		250000	40%	100000
31	160000
32	170000
33	180000
34	190000
35	200000
36	210000
37	220000
38	230000
39	240000
40	250000
41	260000
42	270000
43	280000
44	3610000
Sheet1

Cell Formulas
Range	Formula
E27	=C27*D27
A44	=SUM(A25:A43)
B22	{=SUM(LOOKUP(A25:A43,C26:D30))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[gaz_chops]

Try (change the ranges to suit)
=SUMPRODUCT(A4:A22,LOOKUP($A$4:$A$22,C5:C9,$D$5:$D$9))

 

[gaz_chops]

=sumproduct(a25:a43,lookup($a$25:$a$43,c26:c30,$d$26:$d$30))

 

[RicardoCubed]

Thanks so much for the help and advancing my excel skills!!!
Really appreciate it!!!

 

[gaz_chops]

You're welcome, yep this is a great place for us all to learn.

 

[RicardoCubed]

Hello out there. This is a variation on prior post.
How would I modify this formula to be able to insert in the Market cell the Market calculation should apply to and have the formula look at the Market selected and calculate the aggregate commission?
Any help appreciated!

Excel 2010
	A	B	C	D	E	F	G
1	Market	LI_N
2	Commission$	112,350
3
4	Monthly Commissionable Rent Schedule
5	Year	Month	Rent$	LI_N	LI_R	NY_N	NY_R
6	1	1	100,000.00	0.07	0.03	0.05	0.05
7	1	2	101,000.00	0.07	0.03	0.05	0.05
8	1	3	102,000.00	0.07	0.03	0.05	0.05
9	1	4	103,000.00	0.07	0.03	0.05	0.05
10	1	5	104,000.00	0.07	0.03	0.05	0.05
11	1	6	105,000.00	0.07	0.03	0.05	0.05
12	1	7	106,000.00	0.07	0.03	0.05	0.05
13	1	8	107,000.00	0.07	0.03	0.05	0.05
14	1	9	108,000.00	0.07	0.03	0.05	0.05
15	1	10	109,000.00	0.07	0.03	0.05	0.05
16	1	11	110,000.00	0.07	0.03	0.05	0.05
17	1	12	111,000.00	0.07	0.03	0.05	0.05
18	2	13	112,000.00	0.07	0.03	0.04	0.04
19	2	14	113,000.00	0.07	0.03	0.04	0.04
20	2	15	114,000.00	0.07	0.03	0.04	0.04
Sheet1

Cell Formulas
Range	Formula
B2	=SUMPRODUCT($C$6:$C$365,LOOKUP($B$6:$B$365,$B$6:$B$365,$D$6:$D$365))

 

[gaz_chops]

Hi, try
=SUMPRODUCT($C$6:$C$365,INDEX($D$6:$G$365,,MATCH($B$1,$D$5:$G$5,0)))

 

[RicardoCubed]

Simply AMAZING - so elegant!!!! I thought maybe a vlookup.
Thanks again!!!!!

 

[gaz_chops]

You're welcome.