Conditional sums for adjacent cells

[Eric]

Hello--I am puzzled about whether or not excel can help me with this
function. Here's what my data is like:

Part # Quantity
A1 5
A1 1
A1 2
A2 3
A2 7
A3 1
A3 1

What I'd like to be able to do is find out the total quantity of part A1
(which in this case would be 8), part A2 (10), and part A3 (2). Is there a
formula I can use for this? I'm confused because the range for the sum is
conditional based on the value that appears in column 1.

Thanks in advance for the help--this discussion group has been extremely
helpful for me already.

 

[Martin Fishlock]

Have a look at the sumif function.

Try =sumif(a:a,"A1",b:b)

 

[Trevor Shuttleworth]

=SUMIF(A:A,A2,B:B)

A2 is a cell reference, not a Part Number here

Regards

Trevor

 

[Eric]

Thanks a lot--the SUMIF formula is working.

I have a secondary question--in the worksheet I am working in, there are
thousands of part numbers. Is there any way to avoid having to manually enter
each of those into a SUMIF formula?

 

[George Nicholson]

Trevor said that A2 was a cell reference, not a part number [for it to be a
part number it would have to be SUM(A:A,"A2",B:B) which is not what you
want]. If you put Trevor's non-quote formula into column C & copy down you
should get the proper results without entering part numbers.

HTH,

 

[Trevor Shuttleworth]

To add to George's reply, you could use an Advanced Filter on the Part
Number column to extract a list of unique Part Numbers. You could then use
the unique Part Number column rather than the original Part Number column to
do your SUMIF

Regards

Trevor

 

[Eric]

That works great--thanks a bunch.

One more question--the cells I'm working with have alpha-numeric codes, and
excel for some reason always reads "0E000013" very strangely. It tries to
turn it into a power (like "0.00E+00"). I formatted the column to be text so
it doesn't modify the code, but for some reason it is disrupting the SUMIF
formula. Even though there is only 1 quantity of "0E000013," it says there
are 30, and it's doing that for other "0E..." codes. Any idea what's going on
here or how to fix it?

Thanks again--this board has been immensely helpful.

Trevor said that A2 was a cell reference, not a part number [for it to be a
part number it would have to be SUM(A:A,"A2",B:B) which is not what you
want]. If you put Trevor's non-quote formula into column C & copy down you
should get the proper results without entering part numbers.

HTH,

Thanks a lot--the SUMIF formula is working.

I have a secondary question--in the worksheet I am working in, there are
thousands of part numbers. Is there any way to avoid having to manually
enter
each of those into a SUMIF formula?