Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need macro to check part# and sum
I'm not a programming or excel expert but desperately need help with a problem. I have a huge spreadsheet that has part numbers and quantity. I need to find the same part number and sum the quantity of that part number. Can this easily be solved? -- jg53 ------------------------------------------------------------------------ jg53's Profile: http://www.excelforum.com/member.php...fo&userid=9550 View this thread: http://www.excelforum.com/showthread...hreadid=532585 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need macro to check part# and sum
=SUMIF(A:A,"part number",B:B)
where A is the part number, B is the quantity -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jg53" wrote in message ... I'm not a programming or excel expert but desperately need help with a problem. I have a huge spreadsheet that has part numbers and quantity. I need to find the same part number and sum the quantity of that part number. Can this easily be solved? -- jg53 ------------------------------------------------------------------------ jg53's Profile: http://www.excelforum.com/member.php...fo&userid=9550 View this thread: http://www.excelforum.com/showthread...hreadid=532585 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need macro to check part# and sum
How is your sheet laid out? Are all the part #s in one column and quantities
all in another? Let's say part#s are in col A and quantities in col B =sumproduct(--(A1:A5000="abc-123"),B1:B1000) Also, you can create a pivot table off your data & that will give you a list of all your unique part #s and sum the quatities for each (DataPivot table) "jg53" wrote: I'm not a programming or excel expert but desperately need help with a problem. I have a huge spreadsheet that has part numbers and quantity. I need to find the same part number and sum the quantity of that part number. Can this easily be solved? -- jg53 ------------------------------------------------------------------------ jg53's Profile: http://www.excelforum.com/member.php...fo&userid=9550 View this thread: http://www.excelforum.com/showthread...hreadid=532585 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need macro to check part# and sum
I dare say this solution won't work because the arrays in SUMPRODUCT must
have the same size. Correct example is: =sumproduct(--(A1:A5000="abc-123"),B1:B5000) HTH -- AP "Duke Carey" a écrit dans le message de ... How is your sheet laid out? Are all the part #s in one column and quantities all in another? Let's say part#s are in col A and quantities in col B =sumproduct(--(A1:A5000="abc-123"),B1:B1000) Also, you can create a pivot table off your data & that will give you a list of all your unique part #s and sum the quatities for each (DataPivot table) "jg53" wrote: I'm not a programming or excel expert but desperately need help with a problem. I have a huge spreadsheet that has part numbers and quantity. I need to find the same part number and sum the quantity of that part number. Can this easily be solved? -- jg53 ------------------------------------------------------------------------ jg53's Profile: http://www.excelforum.com/member.php...fo&userid=9550 View this thread: http://www.excelforum.com/showthread...hreadid=532585 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need macro to check part# and sum
And SUMPRODUCT is overkill when there is only one condition.
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "Ardus Petus" wrote in message ... I dare say this solution won't work because the arrays in SUMPRODUCT must have the same size. Correct example is: =sumproduct(--(A1:A5000="abc-123"),B1:B5000) HTH -- AP "Duke Carey" a écrit dans le message de ... How is your sheet laid out? Are all the part #s in one column and quantities all in another? Let's say part#s are in col A and quantities in col B =sumproduct(--(A1:A5000="abc-123"),B1:B1000) Also, you can create a pivot table off your data & that will give you a list of all your unique part #s and sum the quatities for each (DataPivot table) "jg53" wrote: I'm not a programming or excel expert but desperately need help with a problem. I have a huge spreadsheet that has part numbers and quantity. I need to find the same part number and sum the quantity of that part number. Can this easily be solved? -- jg53 ------------------------------------------------------------------------ jg53's Profile: http://www.excelforum.com/member.php...fo&userid=9550 View this thread: http://www.excelforum.com/showthread...hreadid=532585 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need macro to check part# and sum
Col A Part # Col B Qty Col C this is where we want the sum of the part # to show up -- jg53 ------------------------------------------------------------------------ jg53's Profile: http://www.excelforum.com/member.php...fo&userid=9550 View this thread: http://www.excelforum.com/showthread...hreadid=532585 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need macro to check part# and sum
exactly what I gave you!
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "jg53" wrote in message ... Col A Part # Col B Qty Col C this is where we want the sum of the part # to show up -- jg53 ------------------------------------------------------------------------ jg53's Profile: http://www.excelforum.com/member.php...fo&userid=9550 View this thread: http://www.excelforum.com/showthread...hreadid=532585 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need macro to check part# and sum
Bob, I copied and pasted your formula into the spreadsheet but when I drag it down I get nothing but 0's. Am I missing something. Do the fields have to be formatted as numbers or is general okay? -- jg53 ------------------------------------------------------------------------ jg53's Profile: http://www.excelforum.com/member.php...fo&userid=9550 View this thread: http://www.excelforum.com/showthread...hreadid=532585 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
need macro to check part# and sum
You need to reference the correct part number, like so
=SUMIF(A:A,A1,B:B) By dragging it down in this way means that many of the sums will be repeated in column C. May be better to build a list of part numbers in say M1:M100, and use =SUMIF(A:A,M1,B:B) in C1, and drag that down. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jg53" wrote in message ... Bob, I copied and pasted your formula into the spreadsheet but when I drag it down I get nothing but 0's. Am I missing something. Do the fields have to be formatted as numbers or is general okay? -- jg53 ------------------------------------------------------------------------ jg53's Profile: http://www.excelforum.com/member.php...fo&userid=9550 View this thread: http://www.excelforum.com/showthread...hreadid=532585 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|