Variable Calculation

[Libra0099]

Dear All,

I need a formula which can Calculate the result in cell “E27”. Furthermore this should be flexible if I change the no of unit it should give the desired results. For example in cell 10, 12, 16 & 21

Your hlep will be very much appreciated and I am in need of this very badly.

Excel Workbook
	D	E	F	G	H
3	Vendor	Upto 15 (Units)	From 15 to 30 (Units)	From 30 to 40 (Units)	More than 40 (Units)
4	*	*	*	*	*
5	A	* * * * * * * * * * *225	* * * * * * * * * * *250	* * * * * * * * * * *260	* * * * * * * * * * *290
6	*	*	*	*	*
7	*	*	*	*	*
8	Units	Total Amount	*	*	*
9	*	*	*	*	*
10	14	* * * * * * * * * 3,150	*	*	*
11	*	*	*	*	*
12	18	* * * * * * * * * 4,125	*	*	*
13	*	*	* * * * * * * * * 3,375	*	*
14	*	*	* * * * * * * * * * *750	*	*
15	*	*	*	*	*
16	35	* * * * * * * * * 8,425	*	*	*
17	*	*	* * * * * * * * * 3,375	*	*
18	*	*	* * * * * * * * * 3,750	*	*
19	*	*	* * * * * * * * * 1,300	*	*
20	*	*	*	*	*
21	45	* * * * * * * * * 9,725	*	*	*
22	*	*	* * * * * * * * * 3,375	*	*
23	*	*	* * * * * * * * * 3,750	*	*
24	*	*	* * * * * * * * * 2,600	*	*
25	*	*	* * * * * * * * * 1,450	*	*
26	*	*	*	*	*
27	100	* * * * * * * * * 9,725	*	*	*
28	*	*	* * * * * * * * * 3,375	*	*
29	*	*	* * * * * * * * * 3,750	*	*
30	*	*	* * * * * * * * * 2,600	*	*
31	*	*	* * * * * * * *17,400	*	*
Sheet1

 

[Libra0099]

Any LUCK!

Sorry for expiditing as I am stuck at my work....

 

[shg]

      -------A------- --B--- -C- -D- -E- -F- G --H-- --I---
  1       Vendor      helper  0  15  30  40    Units Amount
  2   Acme Mousetraps        225 250 260 290      35   8425

The formula in I2 is

=SUMPRODUCT((H2 > $C$1:$F$1) * (H2 - $C$1:$F$1) * (C2:F2 - B2:E2))

 

[Libra0099]

Thanks a lot!