Find the most occurring values in Column A and consolidating each value's quantity

Hi All,

As part of my work as an intern, my company wants me to look at all of the office supplies ordered by their retail stores and find out which items are most ordered.

The invoice excel file I received from our office supplier has a column for the Item # and Quantity of that item. However, there are multiple occurrences of each Item # because this invoice is itemized by store. Is there a way to consolidate all of the values in the Item # with each other to remove the duplicates while also consolidating all of the quantities of each?

For example:
Office Supplies 2.PNG

The invoice is much longer and has many more different Item #s and many more duplicates but this is more or less the same format.

I used COUNTIF with an advanced filter with Unique Records Only selected but I realized that only gave me the count of the Item #s and didnt factor in the Quantity of each Item # ordered by each retail location.

Thank you!

You don't say what version of Excel you have but there are a number of ways.

You could use formulas, probably a SUMIF formula
or you could use a pivot table
Set the Item # as a row item and SUM of Quantity in Data Field.
I'd recommend the Pivot table as it is easily rearraigned to gleam additional information.