Formula Help Needed -- Not exactly sure what I need

[patreilly441]

Hello, Any help will be greatly appreciated . Using my example below, I want a formula to look for the Territory ID in Table 2 and find it it Table 1 and when it is found sum up only Value1, Value2 and Value5 (Columns B, C, F) for that row.

In the example -- the answer is 9. I can't figure out the formula that I would use in Table 2 . I have tried SumIf, Vlookup, combinations and i am just not getting it done.

Table 1

Territory ID	Value1	Value2	Value3	Value4	Value5
11	1	1	1	1	1
12	1	1	1	1	1
13	1	1	1	1	1
14	1	1	1	1	1
15	1	1	1	1	1
11	1	1	1	1	1
12	1	1	1	1	1
13	1	1	1	1	1
14	1	1	1	1	1
15	1	1	1	1	1
11	1	1	1	1	1
12	1	1	1	1	1
13	1	1	1	1	1

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

Table 2

Territory ID	Sum
11	formula?
12
13

<tbody>
</tbody>

Thank you very much. Pat

 

[JoeMo]

If the numbers under the value columns are always 1 you do a countif on the ID column and multiply it by the number of value columns you want to include. Or, you can just string together sumifs like this:

Excel Workbook
	A	B	C	D	E	F
1	Territory ID	Value1	Value2	Value3	Value4	Value5
2	11	1	1	1	1	1
3	12	1	1	1	1	1
4	13	1	1	1	1	1
5	14	1	1	1	1	1
6	15	1	1	1	1	1
7	11	1	1	1	1	1
8	12	1	1	1	1	1
9	13	1	1	1	1	1
10	14	1	1	1	1	1
11	15	1	1	1	1	1
12	11	1	1	1	1	1
13	12	1	1	1	1	1
14	13	1	1	1	1	1
15	*	*	*	*	*	*
16	*	*	*	*	*	*
17	Territory ID	Sum	*	*	*	*
18	11	9	*	*	*	*
19	12	9	*	*	*	*
20	13	9	*	*	*	*
Sheet11

 

[patreilly441]

Joe -- thank you so much for the very fast response the sumif is exactly what i needed. as soon as i saw your formula i realized my mistake was doing one sumif and trying to ask it to look at everything at once rather than string 3 statements together. Thank you, thank you, thank you! Pat