# sum of duplicates

#### zxmax

##### New Member
Greeting all,
i have a sheet that has 2 colums, colum 1 has a product name , and colum 2 has quantanty
In colum 1 we have duplicates product name with different quantaty> so how do i Combine and sum duplicate (
eg:
product qty
hats 2
hats 5
boots 3
boots 1
----------------
want it to look like:
product qty
hats 7
boots 4

i'm a rooky at this

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Use SUMIF

=SUMIF(A1:A10,"hats",B1:B10)

Hi, zxmax. Welcome to the Bored.

Try this SUMIF() formula:
Book1
ABCD
1hats2
2hats5
3boots3
4boots1
5
6hats7=SUMIF(\$A\$1:\$A\$4,A6,\$B\$1:\$B\$4)
7boots4=SUMIF(\$A\$1:\$A\$4,A7,\$B\$1:\$B\$4)
Sheet2

P.S. You have at your service Barry to the Second Power, a.k.a Barry Squared or Square Barry. How can you go wrong?

that is great,
but lets assum that its a very very long excel sheet, and its hard to keep track of the colum name and row number

is there a more global solution , without puting the clolum names in the forumal

Use a pivot table.

how do you use that ?

What exactly do you mean by "and it's hard to keep track of the column name.."? Do you keep on inserting/deleting columns? Are there multiple sections with the data in different columns?

inventory data for max.xls
ABCD
14040300
24040345
34209165
44209200
54263178
6427071
74305106
8430632
94306-32
104602350
114603528
12460390
1346039
14460440
15460420
1646043
174605200
184605160
19460850
20461066
21462992
22463920
2346394
24466920
25467032
26467032
27467032
28467032
29467032
30467032
310505-BLW-5AS1
320505-BLW-7AL1
330505-BLW-8AXL10
340510-BLK-5AS23
350510-BLK-5AS8
360510-BLK-5AS144
370510-BLK-6AM6
380510-BLK-6AM111
390510-BLK-7AL83
400510-BLK-7AL163
410510-BLK-8AXL12
420510-FOR-5AS129
430510-FOR-6AM58
440510-FOR-8AXL46
450510-GLD-5AS20
new inventory count

the list is bigger then that,
what do you guys think ?

Seems to be the same layout as in the "hats/boots" example. the formula in E2, copied down, is =SUMIF(\$A\$2:\$A\$46,D2,\$B\$2:\$B\$46)

If you're going to be adding products and quantities to columns A & B, then use this: =SUMIF(A:A,D2,B:B)

<SCRIPT language=JavaScript src="http://www.interq.or.jp/sun/puremis/colo/popup.js"></SCRIPT><CENTER><TABLE cellSpacing=0 cellPadding=0 align=center><TBODY><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=#0c266b colSpan=6><TABLE width="100%" align=center border=0><TBODY><TR><TD align=left><FONT color=white>Microsoft Excel - Book1</FONT></TD><TD style="FONT-SIZE: 9pt; COLOR: #ffffff; FONT-FAMILY: caption" align=right>___Running: xl2000 : OS = Windows XP </FONT></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; HEIGHT: 25px" bgColor=#d4d0c8 colSpan=6><TABLE width="100%" align=center border=0 VALIGN="MIDDLE"><TBODY><TR><TD style="FONT-SIZE: 10pt; COLOR: #000000; FONT-FAMILY: caption">(<U>F</U>)ile (<U>E</U>)dit (<U>V</U>)iew (<U>I</U>)nsert (<U>O</U>)ptions (<U>T</U>)ools (<U>D</U>)ata (<U>W</U>)indow (<U>H</U>)elp (<U>A</U>)bout</TD><TD vAlign=center align=right><FORM name=formCb658016><INPUT onclick='window.clipboardData.setData("Text",document.formFb431795.sltNb305190.value);' type=button value="Copy Formula" name=btCb074442></FORM></TD></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid" bgColor=white colSpan=6><TABLE border=0><TBODY><TR><FORM name=formFb431795><TD style="WIDTH: 60px" align=middle bgColor=white><SELECT onchange="document.formFb431795.txbFb428726.value = document.formFb431795.sltNb305190.value" name=sltNb305190><OPTION value==SUMIF(\$A\$2:\$A\$46,D2,\$B\$2:\$B\$46) selected>E2<OPTION value==SUMIF(\$A\$2:\$A\$46,D3,\$B\$2:\$B\$46)>E3<OPTION value==SUMIF(\$A\$2:\$A\$46,D4,\$B\$2:\$B\$46)>E4<OPTION value==SUMIF(\$A\$2:\$A\$46,D5,\$B\$2:\$B\$46)>E5<OPTION value==SUMIF(\$A\$2:\$A\$46,D6,\$B\$2:\$B\$46)>E6<OPTION value==SUMIF(\$A\$2:\$A\$46,D7,\$B\$2:\$B\$46)>E7<OPTION value==SUMIF(\$A\$2:\$A\$46,D8,\$B\$2:\$B\$46)>E8<OPTION value==SUMIF(\$A\$2:\$A\$46,D9,\$B\$2:\$B\$46)>E9<OPTION value==SUMIF(\$A\$2:\$A\$46,D10,\$B\$2:\$B\$46)>E10<OPTION value==SUMIF(\$A\$2:\$A\$46,D11,\$B\$2:\$B\$46)>E11<OPTION value==SUMIF(\$A\$2:\$A\$46,D12,\$B\$2:\$B\$46)>E12<OPTION value==SUMIF(\$A\$2:\$A\$46,D13,\$B\$2:\$B\$46)>E13<OPTION value==SUMIF(\$A\$2:\$A\$46,D14,\$B\$2:\$B\$46)>E14<OPTION value==SUMIF(\$A\$2:\$A\$46,D15,\$B\$2:\$B\$46)>E15<OPTION value==SUMIF(\$A\$2:\$A\$46,D16,\$B\$2:\$B\$46)>E16<OPTION value==SUMIF(\$A\$2:\$A\$46,D17,\$B\$2:\$B\$46)>E17<OPTION value==SUMIF(\$A\$2:\$A\$46,D18,\$B\$2:\$B\$46)>E18<OPTION value==SUMIF(\$A\$2:\$A\$46,D19,\$B\$2:\$B\$46)>E19<OPTION value==SUMIF(\$A\$2:\$A\$46,D20,\$B\$2:\$B\$46)>E20<OPTION value==SUMIF(\$A\$2:\$A\$46,D21,\$B\$2:\$B\$46)>E21<OPTION value==SUMIF(\$A\$2:\$A\$46,D22,\$B\$2:\$B\$46)>E22<OPTION value==SUMIF(\$A\$2:\$A\$46,D23,\$B\$2:\$B\$46)>E23<OPTION value==SUMIF(\$A\$2:\$A\$46,D24,\$B\$2:\$B\$46)>E24<OPTION value==SUMIF(\$A\$2:\$A\$46,D25,\$B\$2:\$B\$46)>E25<OPTION value==SUMIF(\$A\$2:\$A\$46,D26,\$B\$2:\$B\$46)>E26<OPTION value==SUMIF(\$A\$2:\$A\$46,D27,\$B\$2:\$B\$46)>E27<OPTION value==SUMIF(\$A\$2:\$A\$46,D28,\$B\$2:\$B\$46)>E28<OPTION value==SUM(E2:E28)>E29<OPTION value==SUM(B2:B46)>B47</OPTION></SELECT></TD><TD align=right width="3%" bgColor=#d4d0c8>=</TD><TD align=left bgColor=white><INPUT size=80 value==SUMIF(\$A\$2:\$A\$46,D2,\$B\$2:\$B\$46) name=txbFb428726></TD></FORM></TR></TBODY></TABLE></TD></TR><TR><TD style="BORDER-RIGHT: #000000 0.5pt solid; BORDER-TOP: #000000 0.5pt solid; BORDER-LEFT: #000000 0.5pt solid; BACKGROUND-COLOR: #d4d0c8" align=middle width="2%">

thanks alot barry,
just one last question, how did you get the unique colum (D) ?

Replies
0
Views
114
Replies
16
Views
186
Replies
3
Views
282
Replies
0
Views
134
Replies
6
Views
119

1,203,068
Messages
6,053,346
Members
444,654
Latest member
Rich Cohen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back