Vlookup and Sum across multiple tabs

[tfowlerusa]

I have a “Master Parts List” sheet where Part Numbers start in A2 and Part Description starting in B2. I have ~ 25 Sheets each with a different name where there are columns in each. I want to match the Part Number & Description to the Master Part List and sum the total Quantity of parts from E6:E90 from each tab to the corresponding Part Number in the Master Parts List tab. I was trying the following formula but getting #NAME error. I got this from another thread just can't seem to get it to work.


=SUMPRODUCT(SUMIFS(INDIRECT("'"&SHEETS&"'!'E6:E60"),INDIRECT("'"&SHEETS&"'!B6:B90"),A2,INDIRECT("'"&SHEETS&"'!C6:C90"),B2))

Master Parts List : A2 = Part Numbers
B2 = Part Description
C2 = Quantity

Sheet 1 OMCTS : B6:B90 = Part Number
C6:C90 = Description
E6:E90 = Quantity

 

[Aladin Akyurek]

That should be:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SHEETS&"'!E6:E90"),INDIRECT("'"&SHEETS&"'!B6:B90"),A2,INDIRECT("'"&SHEETS&"'!C6:C90"),B2))


Does this succeed?

 

[Dave Patton]

Assuming part numbers are unique
The relevant sheet names are in the range named Sheets.

Excel 2010
	A	B	C	D
1	Part Number	Description	Quantity
2	12	ABC	12	12
3
1a

Cell Formulas
Range	Formula
C2	=SUMPRODUCT(SUMIF(INDIRECT("'"&Sheets&"'!B6:B90"),A2,INDIRECT("'"&Sheets&"'!E6:E90")))
D2	=SUMPRODUCT(SUMIFS(INDIRECT("'"&Sheets&"'!E6:E90"),INDIRECT("'"&Sheets&"'!B6:B90"),A2,INDIRECT("'"&Sheets&"'!C6:C90"),B2))

Named Ranges
Name	Refers To	Cells
Sheets	='1a'!$L$1:$L$2

 

[tfowlerusa]

Part numbers are unique. I tired the formula and got #NAME ? error. I am not sure the the Workbook Defined Names are, is this something I need to add for the formula to work?

 

[Aladin Akyurek]

Part numbers are unique. I tired the formula and got #NAME ? error. I am not sure the the Workbook Defined Names are, is this something I need to add for the formula to work?

SHEETS must refer to a range housing the relevant sheet names. Have a look in the Name Box or in the Name Manager to see whether you have such a name. If not, create it.

 

[tfowlerusa]

I defined names and pasted the formula, now I am getting a #REF ! error.

='Master Parts List'!$G$2:$G$26 ( where I put all the names of each sheet as described)

 

[Aladin Akyurek]

I defined names and pasted the formula, now I am getting a #REF ! error.

='Master Parts List'!$G$2:$G$26 ( where I put all the names of each sheet as described)

Select the range

$G$2:$G$26

in Master Parts List. Go to the Name Box the outer left side of the formula bar and type SHEETS.

Once done so, the formula you have (edited for a small error), i.e.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&SHEETS&"'!E6:E90"),INDIRECT("'"&SHEETS&"'!B6:B90"),A2,INDIRECT("'"&SHEETS&"'!C6:C90"),B2))

should work as intended.

 

[tfowlerusa]

That worked, thank you very much for your time.

 

[Aladin Akyurek]

That worked, thank you very much for your time.

You are welcome. Thanks for the kind update.