Need help with adding multiple vlookups

[criswilk]

I need some help with adding multiple vlookups. What I need to do is use the value in H1 and lookup in column A, C and E returning the value from B, D and F in column I1 added together. I've already got this formula =IF(ISNA(VLOOKUP(A1,B:C,2,0)),"**ERR**",VLOOKUP(A1,B:C,2,0)) but I'm not sure on how to do this for the other columns and add them together all in one formula.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">190003</td><td style="text-align: right;;">3</td><td style="text-align: right;;">190001</td><td style="text-align: right;;">6</td><td style="text-align: right;;">190111</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">190001</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">190004</td><td style="text-align: right;;">1</td><td style="text-align: right;;">190002</td><td style="text-align: right;;">6</td><td style="text-align: right;;">190133</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">190002</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">190007</td><td style="text-align: right;;">1</td><td style="text-align: right;;">190003</td><td style="text-align: right;;">7</td><td style="text-align: right;;">190192</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">190003</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">190013</td><td style="text-align: right;;">3</td><td style="text-align: right;;">190004</td><td style="text-align: right;;">7</td><td style="text-align: right;;">190195</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">190004</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">190015</td><td style="text-align: right;;">3</td><td style="text-align: right;;">190005</td><td style="text-align: right;;">4</td><td style="text-align: right;;">190220</td><td style="text-align: right;;">-1</td><td style="text-align: right;;"></td><td style="text-align: right;;">190005</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">190016</td><td style="text-align: right;;">1</td><td style="text-align: right;;">190006</td><td style="text-align: right;;">1</td><td style="text-align: right;;">190287</td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;">190006</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">190020</td><td style="text-align: right;;">1</td><td style="text-align: right;;">190008</td><td style="text-align: right;;">8</td><td style="text-align: right;;">190316</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">190007</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">190024</td><td style="text-align: right;;">2</td><td style="text-align: right;;">190009</td><td style="text-align: right;;">2</td><td style="text-align: right;;">190334</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">190008</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">190032</td><td style="text-align: right;;">5</td><td style="text-align: right;;">190010</td><td style="text-align: right;;">6</td><td style="text-align: right;;">190581</td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">190009</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Thanks in advance for you any assistance.

 

[Airfix9]

Use SUMIF:

For I1:

=SUMIF(A:A,H1,B:B)+SUMIF(C:C,H1,D:D)+SUMIF(E:E,H1,F:F)

Copy down

 

[criswilk]

Thanks Airfix,

I worked like a dream.

 

[jbeaucaire]

SUMPRODUCT will do it, too:

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	190003	3	190001	6	190111	1		190001	6
2	190004	1	190002	6	190133	1		190002	6
3	190007	1	190003	7	190192	1		190003	10
4	190013	3	190004	7	190195	1		190004	8
5	190015	3	190005	4	190220	-1		190005	4
6	190016	1	190006	1	190287	2		190006	1
7	190020	1	190008	8	190316	1		190007	1
8	190024	2	190009	2	190334	1		190008	8
9	190032	5	190010	6	190581	1		190009	2
Sheet1