Forum Discussion

Teresa Smagacz's avatar
Teresa Smagacz
Copper Contributor
Mar 12, 2018
Solved

VLOOKUP with multiple answers provide one total in cell?

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?

Formulas & Functions
Show and Tell
  • Jamil's avatar
    Jamil
    Mar 13, 2018

    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. 

11 Replies