sumproduct with a look up?

[gobblechops]

Hi,

I need to multiply the inverse of the first table (i.e 0% = 100%, 30% = 70%) by looking up the corresponding cell in the second table (i.e lookup the letter for the row and the month for the column), then sum the total for each of the letters. Below are the two tables and then what the totals should be. I need the formula that goes into the final total cells, please. Thank you.

	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
a	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
b	100%	100%	100%	0%	0%	0%	0%	0%	0%	0%	0%	0%
c	0%	0%	0%	25%	25%	50%	50%	25%	25%	0%	0%	0%
d	30%	30%	30%	30%	10%	0%	0%	0%	0%	0%	0%	0%
e	30%	30%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
f	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
g	50%	25%	25%	0%	0%	0%	0%	0%	0%	50%	25%	25%

	jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
a	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000
b	7,000	7,000	7,000	7,000	7,000	7,000	7,000	7,000	7,000	7,000	7,000	7,000
c	3,500	3,500	3,500	3,500	3,500	3,500	3,500	3,500	3,500	3,500	3,500	3,500
d	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000
e	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000
f	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500
g	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000

	Total
a	60,000
b	63,000
c	35,000
d	42,800
e	91,200
f	102,000
g	60,000

 

[jasonb75]

Is this what you need?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
2	a	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
3	b	100%	100%	100%	0%	0%	0%	0%	0%	0%	0%	0%	0%
4	c	0%	0%	0%	25%	25%	50%	50%	25%	25%	0%	0%	0%
5	d	30%	30%	30%	30%	10%	0%	0%	0%	0%	0%	0%	0%
6	e	30%	30%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
7	f	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%	0%
8	g	50%	25%	25%	0%	0%	0%	0%	0%	0%	50%	25%	25%
9
10		jan	feb	mar	apr	may	jun	jul	aug	sep	oct	nov	dec
11	a	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000	5,000
12	b	7,000	7,000	7,000	7,000	7,000	7,000	7,000	7,000	7,000	7,000	7,000	7,000
13	c	3,500	3,500	3,500	3,500	3,500	3,500	3,500	3,500	3,500	3,500	3,500	3,500
14	d	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000	4,000
15	e	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000	8,000
16	f	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500	8,500
17	g	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000	6,000
18
19
20	a	60,000.00
21	b	63,000.00
22	c	35,000.00
23	d	42,800.00
24	e	91,200.00
25	f	102,000.00
26	g	60,000.00
Sheet1

Cell Formulas
Range	Formula
B20:B26	B20	=SUMPRODUCT((1-INDEX($B$2:$M$8,MATCH(A20,$A$2:$A$8,0),0))*INDEX($B$11:$M$17,MATCH(A20,$A$11:$A$17,0),0))

 

[gobblechops]

Perfect! Thanks

 

[Dave Patton]

try
=SUM((1-INDEX($B$2:$M$8,MATCH(A20,$A$2:$A$8,0),0))*INDEX($B$11:$M$17,MATCH(A20,$A$11:$A$17,0),0))