Sum the total of duplicated items in a list

[bearcub]

Is the a formula that will count the total number of unique items in a list

For example, if I have a list where 5 items appear more than once. One item might appear 3 times, another appear 2, etc.

I don't need to find if an item is duplicated I just need to sum the number of duplicate items (which would be 5). Is there a formula that will do that?

Thank you in advance,

Michael

 

[richiebkerry]

Hello, I have a list of data ( thousands of lines) containing sales information on customers. This data contain week numbers (column A), postcode (column J).

What I want to do is count how many unique postcodes are in each week so that I can add that information into a current pivot table which provides sales info etc etc.

For example,

In week 1 if I have NN15 7QS in more than once I only want to count it once, I I don't want to count the 5 occurrences of that postcode.I am currently using, =IF(SUMPRODUCT(($A$9558:$A9660=A9660)*($J$9558:$J9660=J9660))>1,0,1) which doesn't work as it is selecting prior weeks. I have also tried creating a new pivot table using the "Distinct Count"[FONT=Helvetica Neue, Helvetica, Arial, sans-serif] function but when I try and add sales information into the pivot and use SUM it says "We can't summarize the field with Sum because it's not a supported calculation for Text data types".[/FONT]

[FONT=Helvetica Neue, Helvetica, Arial, sans-serif]Any help as always is greatly appreciated.[/FONT]

 

[Aladin Akyurek]

@ richiebkerry

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(postcodes<>"",IF(weeknums=wnum,MATCH(postscodes,postcodes,0))),ROW(INDEX(postcodes)-ROW(INDEX(postcodes,1,1))+1),1))

where wnum is a week number of interest.