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

 

[bearcub]

I think what I'm asking is not summing but counting the number of items that are duplicated, which would be 5.

 

[Fluff]

How about
=ROWS(A1:A7)-SUMPRODUCT((A1:A7<>"")/COUNTIF(A1:A7,A1:A7))

 

[bearcub]

Yes, this works great. This counts all the members in the list and then subtracts the duplicated items.

But, this there a formula with using the subtraction operation that will just count the members that are duplicated, not the number of duplicated items?

Thank you,

Just curious if there is another way of counting 1 for each member that is duplciated, not the the number times they're duplicated.

Michael

 

[Fluff]

If there is way of just counting 1 for each duplicated member, I don't know how.

 

[mikerickson]

Perhaps
=SUMPRODUCT(IFERROR(1/(--(COUNTIF(A1:A20,A1:A20)>1)*COUNTIF(A1:A20,A1:A20)),0))

entered with Ctrl-Shift-Enter

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(A1:A20,A1:A20)>0,1))

delivers the number of distinct/unique numbers which occur in A1:A20.

=SUM(IF(FREQUENCY(A1:A20,A1:A20)>1,1))

delivers the number of distinct/unique numbers which occur more than once in A1:A20.

 

[bearcub]

Thank you Mike for the formula. It works perfectly. I thought there would be 5 items but turns out there are 3 items that are have a total of 5 duplicates (in my particular list).

Michael

 

[bearcub]

Hi Aladin,

Thank you for the formulas. I modified the formulas to fit my criteria range but I'm getting zeros after entering CSE.

=SUM(IF(FREQUENCY(Groceries,Groceries)>0,1))
=SUM(IF(FREQUENCY(Groceries,Groceries)>1,1))

Groceries is named range from B4:B52. Is something missing from my list? Like maybe my range should be from B4:B53?

Michael

 

[Aladin Akyurek]

Hi Aladin,

Thank you for the formulas. I modified the formulas to fit my criteria range but I'm getting zeros after entering CSE.

=SUM(IF(FREQUENCY(Groceries,Groceries)>0,1))
=SUM(IF(FREQUENCY(Groceries,Groceries)>1,1))

Groceries is named range from B4:B52. Is something missing from my list? Like maybe my range should be from B4:B53?

Michael

Grocesies should consist of numbers. Otherwise:

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(Groceries<>"",MATCH(Groceries,Groceries,0)),ROW(Groceries)-ROW(INDEX(Groceries,1,1))+0)>0,1))

=SUM(IF(FREQUENCY(IF(Groceries<>"",MATCH(Groceries,Groceries,0)),ROW(Groceries)-ROW(INDEX(Groceries,1,1))+0)>1,1))

Note that these FREQUENCY formulas work faster than the other alternative formulations.