Formula help, please

[elvezrulz]

I need a formula that can do what VLOOKUP does, but can sum the results. I'm looking up products on an inbound PO's report, so on the report I need all the inbound PO quantities to be summed up into one field. Does anyone know how to tackle that?

 

[elvezrulz]

IT WORKS!! I had to slightly change the forumula. thanks!!!!!!!

 

[markmzz]

OK, I'll try:

Here's a very simplified version. Sheet 1 is a summary, it's the Inbound cell I'm trying to populate.

PAGE ONE SUMMARY
A B C
FG NUMBER Description Inbound
000001 Product Description #N/A


The sheet that is the lookup for the Inbound might have several lines within the sheet where the 0000001 product has many, many PO's that are inbound. I'm trying to both do a VLOOKUP to find the products, but also sum up the total. What I'm getting populated on sheet 1 is 171, just the first instance of the 0000001 product, instead of 1686 which is all of the 000001 PO's summed. This is very simplified, sheet one might have 100 products, while the inbound sheet might be over 500 lines with many PO's for any specific product.
A B C
0000001 PRODUCT DESCRIPTION 171
0000001 PRODUCT DESCRIPTION 129
0000001 PRODUCT DESCRIPTION 200
0000001 PRODUCT DESCRIPTION 450
0000001 PRODUCT DESCRIPTION 355
0000001 PRODUCT DESCRIPTION 200
0000001 PRODUCT DESCRIPTION 181


Thanks in advance for any help!

Maybe this:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; 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></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Column01</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Column02</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Column03</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Column01</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Column02</TD><TD style="TEXT-ALIGN: center; FONT-WEIGHT: bold">Result</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center">6309X</TD><TD>Description</TD><TD style="TEXT-ALIGN: center">112</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">8151X</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Description1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">1606</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">8151X</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Description1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">493</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: center">4340X</TD><TD>Description</TD><TD style="TEXT-ALIGN: center">290</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">6476X</TD><TD>Description</TD><TD style="TEXT-ALIGN: center">866</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: center">2893X</TD><TD>Description</TD><TD style="TEXT-ALIGN: center">122</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">8151X</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Description1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">788</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: center">8226X</TD><TD>Description</TD><TD style="TEXT-ALIGN: center">273</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: center">6952X</TD><TD>Description</TD><TD style="TEXT-ALIGN: center">815</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">8151X</TD><TD style="BACKGROUND-COLOR: #c5d9f1">Description1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">325</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: center">4023X</TD><TD>Description</TD><TD style="TEXT-ALIGN: center">159</TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: center"></TD><TD style="TEXT-ALIGN: right"></TD></TR></TBODY></TABLE>Sheet11


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>G2</TH><TD style="TEXT-ALIGN: left">=SUMIF(A2:A11,E2,C2:C11)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz