but i would like to search through a list of product codes that have product
quantities next to them.

i would like excel to automatically add together the quantities of matched
product codes.

for example if i had A1:- 20, A2:- 30, A3:- 20, A1:- 35, A3:- 35

then it would summaries the information to read

A1:- 55
A2:- 30
A3:- 55

hope this makes sense.

Many thanks in advance.

fraser

Hi,
first enter the product codes let's say cell A1= A1, in cell A2 enter
product code A2, let's suppose that your data start in cell A5 to B100. In B1
enter the formula as follow
=sumproduct(--($A$5:$A$100=a1),--$b$5:$b$100)
then copy the formula down to obtain subtotal for product A2

"Fraser B" wrote:

but i would like to search through a list of product codes that have product
quantities next to them.

i would like excel to automatically add together the quantities of matched
product codes.

for example if i had A1:- 20, A2:- 30, A3:- 20, A1:- 35, A3:- 35

then it would summaries the information to read

A1:- 55
A2:- 30
A3:- 55

hope this makes sense.

Many thanks in advance.

fraser

eduardo thanks for your reply,

i may have simplified things too much in my explanation.

i have hundreds and hundreds of different product codes in a very long list.

therefore i was hopeing to find a way of asking excel to search that list
itself without a search name or string to search for. just to find
duplicates and add the quantities together. if there was no dupicates just
to put the one product code and quantity in.

not sure if thats too complicated or not?

"Eduardo" wrote:

Hi,
first enter the product codes let's say cell A1= A1, in cell A2 enter
product code A2, let's suppose that your data start in cell A5 to B100. In B1
enter the formula as follow
=sumproduct(--($A$5:$A$100=a1),--$b$5:$b$100)
then copy the formula down to obtain subtotal for product A2

"Fraser B" wrote:

but i would like to search through a list of product codes that have product
quantities next to them.

i would like excel to automatically add together the quantities of matched
product codes.

for example if i had A1:- 20, A2:- 30, A3:- 20, A1:- 35, A3:- 35

then it would summaries the information to read

A1:- 55
A2:- 30
A3:- 55

hope this makes sense.

Many thanks in advance.

fraser

Try a PivotTable.

http://www.dicks-blog.com/archives/2...e-pivot-table/


Fraser B wrote:
eduardo thanks for your reply,

i may have simplified things too much in my explanation.

i have hundreds and hundreds of different product codes in a very long list.

therefore i was hopeing to find a way of asking excel to search that list
itself without a search name or string to search for. just to find
duplicates and add the quantities together. if there was no dupicates just
to put the one product code and quantity in.

not sure if thats too complicated or not?

"Eduardo" wrote:

Hi,
first enter the product codes let's say cell A1= A1, in cell A2 enter
product code A2, let's suppose that your data start in cell A5 to B100. In B1
enter the formula as follow
=sumproduct(--($A$5:$A$100=a1),--$b$5:$b$100)
then copy the formula down to obtain subtotal for product A2

"Fraser B" wrote:

but i would like to search through a list of product codes that have product
quantities next to them.

i would like excel to automatically add together the quantities of matched
product codes.

for example if i had A1:- 20, A2:- 30, A3:- 20, A1:- 35, A3:- 35

then it would summaries the information to read

A1:- 55
A2:- 30
A3:- 55

hope this makes sense.

Many thanks in advance.

fraser

use SUMIF

=SUMIF(A1:A5,"A1",B1:B5) or

=SUMIF(A1:A5,C1,B1:B5)

where C1 is the cell you type the product code you want to sum

HTH
--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis









"Fraser B" wrote:

but i would like to search through a list of product codes that have product
quantities next to them.

i would like excel to automatically add together the quantities of matched
product codes.

for example if i had A1:- 20, A2:- 30, A3:- 20, A1:- 35, A3:- 35

then it would summaries the information to read

A1:- 55
A2:- 30
A3:- 55

hope this makes sense.

Many thanks in advance.

fraser

I suggest that you post a sample and the result you want

--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis









"Fraser B" wrote:

eduardo thanks for your reply,

i may have simplified things too much in my explanation.

i have hundreds and hundreds of different product codes in a very long list.

therefore i was hopeing to find a way of asking excel to search that list
itself without a search name or string to search for. just to find
duplicates and add the quantities together. if there was no dupicates just
to put the one product code and quantity in.

not sure if thats too complicated or not?

"Eduardo" wrote:

Hi,
first enter the product codes let's say cell A1= A1, in cell A2 enter
product code A2, let's suppose that your data start in cell A5 to B100. In B1
enter the formula as follow
=sumproduct(--($A$5:$A$100=a1),--$b$5:$b$100)
then copy the formula down to obtain subtotal for product A2

"Fraser B" wrote:

but i would like to search through a list of product codes that have product
quantities next to them.

i would like excel to automatically add together the quantities of matched
product codes.

for example if i had A1:- 20, A2:- 30, A3:- 20, A1:- 35, A3:- 35

then it would summaries the information to read

A1:- 55
A2:- 30
A3:- 55

hope this makes sense.

Many thanks in advance.

fraser