Formula Help Needed -- Not exactly sure what I need

P

patreilly441

New Member
Joined
Mar 16, 2017
Messages
2
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 :confused:. I have tried SumIf, Vlookup, combinations and i am just not getting it done.

Table 1
[TABLE="width: 384"]
<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Territory ID[/TD]
[TD="width: 64"]Value1[/TD]
[TD="width: 64"]Value2[/TD]
[TD="width: 64"]Value3[/TD]
[TD="width: 64"]Value4[/TD]
[TD="width: 64"]Value5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]11[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]11[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]11[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="class: xl65, align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]


Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Territory ID[/TD]
[TD]Sum[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]formula?[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Thank you very much. Pat
 
Sort by date Sort by votes
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
ABCDEF
1Territory IDValue1Value2Value3Value4Value5
21111111
31211111
41311111
51411111
61511111
71111111
81211111
91311111
101411111
111511111
121111111
131211111
141311111
15******
16******
17Territory IDSum****
18119****
19129****
20139****
Sheet11
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
Required Transposed Values instead of Row Items of Duplicate Invoice no with Each GSTIN which is displaying the SUM of 12+18+28
Replies
1
Views
115
rk_rk
R
D
  • Solved
Repeat Range of Cells N times with each item in range repeating X times
Replies
5
Views
582
Cubist
Cubist
I
  • Solved
In Excell VBA the set variable change without clear reason
Replies
5
Views
137
Micron
M
E
  • Question Question
Write an xlookup formula across worksheet name
Replies
3
Views
114
coulombevin
coulombevin
R
  • Question Question
Help Formula Countif or Countifs
Replies
6
Views
138
Cubist
Cubist

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top