Sum a column, excluding the duplicates.

[Preven123]

I have a worksheet that consists of 39729 rows, with many duplications. I need to supply a total of column "D". The total must exclude the duplicates. I hope this makes sense. Thank you.

 

[Joe4]

Welcome to the Board!

If you are using Excel 2007 or newer, on the Data Menu, under the Data Tools ribbon, there is a "Remove Duplicates" button.
You can use that to remove your duplicates, then run your SUM.

If you need to maintain all your original data, I would copy all your data to a new tab, then do the steps above on the copy.

 

[jim may]

here's a formula - but needs assignment of 2 range names...

Excel 2012
	A	B	C	D
1	119		Range Names:	refers to:
2	471		list	A1:A5
3	119	<<< Duplicate	list_start	A1
4	553
5	119
6	1143	<<< Sum EXCLUDING Duplicates
Sheet1

Cell Formulas
Range	Formula
A6	{=SUM(IF(IF(MATCH(list, list, 0)=(ROW(list)-ROW(list_start)+1), (ROW(list)-ROW(list_start)+1), 0)<>0, list, ""))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
list	=Sheet1!$A$1:$A$5
list_start	=Sheet1!$A$1

 

[Preven123]

Thank you so much

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(ISNUMBER(D1:D5),MATCH(D1:D5,D1:D5,0)),ROW(D1:D5)-ROW(D1)+1),D1:D5))