Conditional sums for adjacent cells
 

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.

Conditional sums for adjacent cells
 

Have a look at the sumif function.

Try =sumif(a:a,"A1",b:b)
--
Hope this helps
Martin Fishlock, Bangkok, Thailand
Please do not forget to rate this reply.


"Eric" wrote:

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.

Conditional sums for adjacent cells
 

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

A2 is a cell reference, not a Part Number here

Regards

Trevor


"Eric" wrote in message
...
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.

Conditional sums for adjacent cells
 

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?


"Trevor Shuttleworth" wrote:

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

A2 is a cell reference, not a Part Number here

Regards

Trevor


"Eric" wrote in message
...
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.

Conditional sums for adjacent cells
 

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,

"Eric" wrote in message
...
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?


"Trevor Shuttleworth" wrote:

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

A2 is a cell reference, not a Part Number here

Regards

Trevor


"Eric" wrote in message
...
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.

Conditional sums for adjacent cells
 

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" wrote in message
...
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?


"Trevor Shuttleworth" wrote:

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

A2 is a cell reference, not a Part Number here

Regards

Trevor


"Eric" wrote in message
...
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.

Conditional sums for adjacent cells
 

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.


"George Nicholson" wrote:

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,

"Eric" wrote in message
...
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?


"Trevor Shuttleworth" wrote:

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

A2 is a cell reference, not a Part Number here

Regards

Trevor


"Eric" wrote in message
...
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.