Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating only non-empty cells... | Excel Worksheet Functions | |||
Conditional Formatting Merged Cells | Excel Worksheet Functions | |||
checking that cells have a value before the workbook will close | Excel Worksheet Functions | |||
Creating a conditional format for a cell based on another cell's v | Excel Discussion (Misc queries) | |||
Calculate percentage based on cells with conditional formatting | New Users to Excel |