Add Duplicates

  • Thread starter Thread starter tbh
  • Start date Start date
T

tbh

I am trying to add the quantity from column B if column A is a
duplicate. I have thousands of rows and any help would be
appreciated.

A B
Text 1
Text 1

Thanks!
 
Do your mean: Answer1= sum of all the B values when A = cat; Answer 2= sum
of all B values when A = dog, etc.

Put the unique text values in column E
In F1 use =SUMIF($A$1:$A$500, E1, $B$1:$B$500)
copy down the column

or make a pivot table

http://www.cpearson.com/excel/pivots.htm
http://peltiertech.com/Excel/Pivots/pivotstart.htm
http://www.contextures.com/xlPivot02.html
http://www.ozgrid.com/Excel/excel-pivot-tables.htm
http://www.techonthenet.com/excel/pivottbls/index.htm
http://www.dicks-blog.com/archives/2005/06/23/download-pivottable-parameters/

best wishes
 
tbh said:
I am trying to add the quantity from column B if column A is a
duplicate.  I have thousands of rows and any help would be
appreciated.

A     B
Text    1
Text    1
Click to expand...

=SUMIF(A1:A10000,x,B1:B10000)

where x could be A1, in which case this formula would sum all cells in
B1:B10000 with corresponding column A values equal to cell A1.
 
Hi,

Try something like this:

=IF(COUNTIF(A1:A8,H1)>1,SUMPRODUCT(--(A1:A8=H1),B1:B8),"")

or

=IF(COUNTIF(A1:A8,H1)>1,SUMIF(A1:A8,H1,B1:B8),"")

where the item you are checking for is in H1.

On the other hand if you are trying to sum the values in B for all items
that are duplicated then

=SUMPRODUCT(--(COUNTIF(A1:A8,A1:A8)>1),B1:B8)

If this helps, please click the Yes button.
 
Back
Top