Forum Discussion
VLOOKUP with multiple answers provide one total in cell?
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.
Yes, what formula would I use?
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.
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?
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)
- Hoppers (highlighted in yellow is where i need formulas)
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
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
- Resin tab (in red) is where i need the formulas within the following columns: