Looking for the correct formula or function

[Tresix]

Hi,

Within a range of data, I have item numbers, some of which are duplicate (items sold in different weeks). Within the same range, I also have units sold. I'm trying to find a formula that will extract all units sold for each individual items number including duplicates.

Example:

Cell A2 Item # 1234 Units Sold 1
Cell A3 Item # 1234 Units Sold 7
Cell A4 Item # 2345 Units Sold 3...

Item # 1234 should populate 8 units, I was using a Vlookup, but it's not working. The formula is simply returning 1 units, it's not finding the other 7 units. I'm positive that I'm using the wrong formula. Can anyone help?

Thanks,

Tresix

[kev_]

One way:
With quantities in column B and item number in column A
=SUMIF(A:A,"1234",B:B) [Text]
=SUMIF(A:A,1234,B:B) [Numeric]

look in column A for all cells ="1234" and sum the quantities found in column B
Instead of 1234, you can use a cell reference

Note - the 2 ranges must be identical in size

[Aardigspook]

Assuming you want to have a list of the different item numbers, with their totals, try this:
Put this in D2:

Please Login or Register  to view this content.

It's an array formula, so enter using Ctrl-Shift-Enter not just enter. You should see curly brackets {} appear round the formula in the formula bar - don't try to enter them yourself.
Drag it down as far as you need.
Change the range $A$2:$A$10 to match your actual data. If you want to put this in another column than D, just change the $D$1:D1 range to that column.

Then in E2, use this:

Please Login or Register  to view this content.

Change both D2 instances if you've put the previous formula in another column (I think that's obvious, but...).


Having said all of that, the easiest way to do this might be to use a pivot table instead.

Select your range (A1:B10 or whatever - include your header row). On the 'Insert' tab on the ribbon, click 'Pivot Table'. You can choose to put it on the same worksheet or a new one. When you've created it, look on the right hand side and you'll see your column names as 'Fields'. Drag the 'Item number' into the 'Row labels' box below, then drag the 'Units sold' into the 'Values' box. The pivot table will then show you the totals, without you having to do anything else.


The attached file shows both methods (formulae and pivot table).
Hope that helps.