Combining data across multiple sheets onto one summary sheet

Is it possible to create a summary sheet that includes all the data from many sheets (in 1 workbook)?

I have a workbook with several sheets of data that I need to have combined into 1 sheet that adds all parts and adds the quantities.

Example:

Sheet 1:
201632130-103 32 EA BRACKET ASSY,TOE KICK LIGHT
201632130-501 1 EA SCREEN
201632130-503 32 EA BRACKET

Sheet 2:
201632117-509 2 EA SPLICE ANGLE
201632120-103 4 EA SUPPORT ASSY.
201632121-513 0 EA FORMED PANEL
201632130-103 32 EA BRACKET ASSY,TOE KICK LIGHT

Sheet 3:
201632112-501 1 EA HINGE
201632112-503 2 EA SUPPORT
201632117-509 2 EA SPLICE ANGLE

Notice how there are a couple parts that occur in more than 1 sheet. Can a summary sheet combine all those parts into one master list and total the quantities for each part?

Any help is greatly apprciated!
Thank you,
Dana

Attached template should provide insight in how to achieve your desired results.

Not quite what I'm looking for but thank you for responding to me.
Dana

Ok, here is a sample of the workbook I have. If you notice on the summary sheet there are part in red. the quantities have been summed from the other sheets they appear on.
Is it possible to make excel read the data in the cells on those other sheets and combine it into one list on the summary sheet?
Does this make sense?
Parts List.xlsx

Thank you,
Dana

You want the Quantity column (Column B) on the Summary sheet to automatically calculate the quantities for each part from all the other worksheets?

I want it to pull all the data from each sheet, or at the least the part numbers and quantities.
I guess I'm asking if excel can search and combine the data from multiple sheets into one master list while totalling all quantities for each part.
Is that a lot to ask for?
Thanks,
Dana

So you want all columns (A through I) consolidated onto one sheet with subtotals by part number?

Yes, that sounds about right :-)

Will Columns C through Column I ever change for any given part number?

I added a "=COUNTIF" column to your summary page, which references as consolidated sheet (easiest way to pull in this info). Let me know if this is what you're looking for. Last sheet is a "=VLOOKUP" for all other columns, if you find that helpful.

That is awesome! This is above my head though. Are you able to explain the formulas so I can understand how you created it?

Thank you so much for your help!
Dana

Sure. Assuming you're looking at Cell C2 on the Summary tab.

"=SUMIF" basically says, look in the combined table (Combined!$A$3:$B$40) for all the occurrences of this part number (Part #170-839, found in Cell A2). If you find the Part #, take sum up all the quantities (which are found in 'Combined!$B$3:$B$40').

Below is MSFT's explanation if you'd like some more examples. Hope this helps!

http://office.microsoft.com/en-us/ex...in=HP010342656

How does it know to read the part and quantity on the other sheets?

That's what the function "=SUMIF" tells excel to do. It basically says, IF you can find this part number (A2) in this table (Combined!$A$3:$B$40), take the SUM of the matching numbers in this column ('Combined!$B$3:$B$40').

Oh I see... you had to create another sheet (Combined) that lists all parts and used that to tell SUMIF where to look. So it can't go sheet by sheet, it has to all be on one sheet already.
Well just bummer!

Thank you so much for your help. I really appreciate it.
Dana

Oh I see... that's why you created the combined sheet. So it can't go sheet by sheet, it all has to be in one place already. Well just bummer! I was hoping that wasn't the case.
The data is entered under a different sheet for each job by someone else. I am responsible for tallying the totals of each part for purchasing purposes. It seems like it would be redundant and more time consuming to have to recreate the combined sheet everythime there is a change or addition to the individual sheets.

Thank you so much for your help. I really appreciate you taking the time!
Dana