Multiple Tab Summary Formula

[EYEMDABEST]

I have a workbook with a summary sheet and multiple sheets for inputing information. The sheets all have part numbers that i need to sum up to the summary sheet. How do I compare the part number on the summary sheet to all of the other tabs and when a match is found add them all together?

SummarySheet
P/N Description Qty
1234 Widget 1 200 This is what I want the formula to calculate
2345 Widget 2 400 This is what I want the formula to calculate

SheetOne
P/N Description Qty
1234 Widget 1 200
2345 Widget 2 100

SheetTwo
P/N Description Qty
2345 Widget 2 300

 

[T. Valko]

I have a workbook with a summary sheet and multiple sheets for inputing information. The sheets all have part numbers that i need to sum up to the summary sheet. How do I compare the part number on the summary sheet to all of the other tabs and when a match is found add them all together?

SummarySheet
P/N Description Qty
1234 Widget 1 200 This is what I want the formula to calculate
2345 Widget 2 400 This is what I want the formula to calculate

SheetOne
P/N Description Qty
1234 Widget 1 200
2345 Widget 2 100

SheetTwo
P/N Description Qty
2345 Widget 2 300

What are the REAL sheet names of the data sheets?

 

[EYEMDABEST]

SheetOne = NonStdReq

SheetTwo = StdReq

 

[T. Valko]

SheetOne = NonStdReq

SheetTwo = StdReq

Try this...

On the Summary sheet...

A2 = PN
B2 = description

Enter this formula in C2:

=SUMPRODUCT(--(NonStdReq!A$2:A$10=A2),--(NonStdReq!B$2:B$10=B2),NonStdReq!C$2:C$10)+SUMPRODUCT(--(StdReq!A$2:A$10=A2),--(StdReq!B$2:B$10=B2),StdReq!C$2:C$10)

 

[EYEMDABEST]

entered the formula but it shows 0's whether there are numbers in the cells or not.

=SUMPRODUCT(--(NonStdReq!A$2:A$10=A2),--(NonStdReq!B$2:B$10=B2),NonStdReq!C$2:C$10)+SUMPRODUCT(--(StdReq!A$2:A$10=A2),--(StdReq!B$2:B$10=B2),StdReq!C$2:C$10)

in this formula do the "--" need to be there like they are or should they be replaced with something else like a cell number or whatever?

 

[T. Valko]

entered the formula but it shows 0's whether there are numbers in the cells or not.

=SUMPRODUCT(--(NonStdReq!A$2:A$10=A2),--(NonStdReq!B$2:B$10=B2),NonStdReq!C$2:C$10)+SUMPRODUCT(--(StdReq!A$2:A$10=A2),--(StdReq!B$2:B$10=B2),StdReq!C$2:C$10)

in this formula do the "--" need to be there like they are or should they be replaced with something else like a cell number or whatever?

Here's a small sample file that demonstrates this.

zzzEYEMDABEST.xls 16kb

http://cjoint.com/?AHbrdAp5dIs

The double unary minus -- are there on purpose. For an explanation of what they do see this:

http://xldynamic.com/source/xld.SUMPRODUCT.html

http://cjoint.com/?AHbrdAp5dIs