Sum of Duplicates Across Multiple Sheets

[MichaelWayne_71]

Hello All,
I searched the forum and was unable to find a simple(?) answer to what I need.

1 workbook with 5 sheets
Column B contains inventory item numbers
Column F contains counts

There are several duplicate entries and I need to consolidate the counts from all sheets so that they can be saved as a .csv and imported into another software. If it helps, this is data from a physical inventory count where items are in multiple locations in the building.
Will a Pivot Table work for this? I am not sure how to export or save the Pivot Table data in the format that I need. Can someone help me with a simple solution?

Any help is appreciated.

 

[pplstuff]

Do you need to keep them as 5 different sheets? If not...

Copy ColB from each of the five sheets and paste into a new sheet, so you have one giant list.
Go to the data tab and hit "remove duplicates"
Then use a simple sumif() to add up all the counts.

I'm not sure if this is exactly what you need, but it seems close. Post a few more details if this isn't what you intended.

 

[MichaelWayne_71]

I could easily put them into one...thanks for the thought.

The only issue is that all of the duplicates from the sheets have counts. so I need to look for item# "1000" in column B and look at the number in column F and add it to a single line

 

[pplstuff]

You can sum the counts easily with
=sumif()+sumif()+sumif()... 1 sumif() for each sheet.

Likewise, you can use:
=vlookup()+vlookup()+vlookup()...

Or..
=index(match())+index(match())...

But I'd go with sumif(), just in case there are some duplicates on the same sheet.

 

[MichaelWayne_71]

I am not familiar with multiple sumif's or multiple vlookup. can you post an example? ...fyi, there are multiple entries on each sheet of the same item.

thanks for the help, by the way.

 

[pplstuff]

Assuming you have all unique item numbers in Sheet6!a1
Sheet6!b1: =sumif(Sheet1!b:b,a1,Sheet1!f:f)+sumif(Sheet2!b:b...)+sumif(Sheet3!b:b...)+...

Change b:b, f:f for each sheet to your ranges. So you'll have 1 sumif() for each of your 5 sheets.