need macro to check part# and sum

[jg53]

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?

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Duke Carey]

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 (Data>Pivot
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
>
>

[Ardus Petus]

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" <[email protected]> a écrit dans le message
de news:[email protected]...
> 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
(Data>Pivot
> 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
> >
> >

[Bob Phillips]

And SUMPRODUCT is overkill when there is only one condition.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ardus Petus" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> a écrit dans le message
> de news:[email protected]...
> > 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
> (Data>Pivot
> > 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
> > >
> > >
>
>

[jg53]

Col A Part #
Col B Qty
Col C this is where we want the sum of the part # to show up

[Bob Phillips]

exactly what I gave you!

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"jg53" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[jg53]

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?

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>