VLOOKUP with multiple answers provide one total in cell?

[Teresa Smagacz]

How do I create a formula to look at another tab, say HOPPERS, and pull any 'total value' column total(s) when i have it look at a specific part which can be listed more than once on the HOPPER tab? Each time a specific part is listed on the HOPPER tab it will have its own "total value," so I want my original tab to be able to grab all the instances the part is listed on the HOPPER tab and provide all its "total value" column amounts, BUT add them up and provide ONE grand total amount on the original tab.

For example, the original tab i'll have the VLOOKUP formula to look at the HOPPER tab and find any part number XYZ. On the HOPPER tab, the part XYZ is listed 4 different times with the values 1, 2, 3, 4. How do i have the formula add up 1+2+3+4 for part XYZ and input 10 in the cell on the original tab?

[#2]

Hi Teresa,

Do you mean something like this?

[#3]

Yes, what formula would I use?

[#4]

Hi Teresa,

For this sample the formula is

=SUMPRODUCT(($A$2:$A$80=$F2)*$B$2:$B$80)

you only shall adjust the ranges.

Sample is attached.

[#5]

Sorry, a little confused. Wouldn't this be more of a nested formula with SUM and also VLOOKUP? Where a cell looks up another tab of information and sums all items matching a certain criteria?

[#6]

I have attached the file called Resin Counting-TMS.  Please notate the following:

• Resin tab (in red) is where i need the formulas within the following columns:
  • Hoppers (highlighted in yellow is where i need formulas)
    • Pulls data from Hoppers tab (highlighted in yellow)
  • Dryers (highlighted in blue is where i need formulas)
    • Pulls data from Dryers tab (highlighted in blue)

For example, within the Resin tab cell K2, I need it to look at A2 to know the part number/criteria to look up, then go to the Hoppers tab and find this part number anywhere within this Hoppers tab and pull all the Total column info (i defined this column as HopperTotal) and put the sum of all of these matching parts within K2 of the Resin tab.

I defined:

• Hopper tab 'Total' column as HopperTotal
• Dryers tab 'Total' column as DryersTotal

[#7]

Just put this =IF(LEFT(A2,7)<>"Serial#",SUMIF(Hoppers!B:B,A2,HopperTotal),"") into the K2 and drag down.

[#8]

That could be like

=IF(LEFT(A2,6)="Serial","",SUMPRODUCT((Hoppers!$B$2:$B$200=Resin!A2)*Hoppers!$G$2:$G$200))

for Hoppers and

=IF(LEFT(A2,6)="Serial","",SUMPRODUCT((Dryers!$B$2:$B$200=Resin!A2)*Dryers!$F$2:$F$200))

for Dryers

as attached

[#9]

Hi,

I'm trying to understand the formula:) What does the 6 (or 7) mean after the A2 column within the LEFT formula?

[#10]

7 in my formula means the length of the characters of Serial#

if you count number of characters in the Serial# it is 7 so it says. if 7 character of text from the left is Serial# then do that conditional SUM, otherwise return nothing.

[#11]

I have defined names for the columns, but the following formula provides an #VALUE! error within cell:

=IF(LEFT(A2,6)="Serial","",SUMPRODUCT((HoppersResin=Resin!A2)*HoppersTotal))

HoppersResin is product column from the Hoppers tab.

HoppersTotal is grand total column from the Hoppers tab.

[#12 · Accepted solution]

you should use it like this.

SUMIF is faster than SUMPRODUCT
=IF(LEFT(A2,6)="Serial","",SUMIF(HoppersResin,A2,HoppersTotal))

plz make sure that your named ranges have equal number of rows in both named ranges used in the formula.