Forum Discussion

Anıl Adaş's avatar
Anıl Adaş
Brass Contributor
Apr 15, 2019
Solved

Remove Dublicates and Count (help pls)

I have a column and I simply need to count them with the help of an excel formula.   I add an example excel file to explain case easy. There are some numbers which I cannot remove but also cannot c...
admin
excel
Formulas and Functions
Macros and VBA
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Apr 15, 2019

    Anıl Adaş , if calculate how many values have duplicates, that's

    =SUMPRODUCT((A2:INDEX(A:A,COUNTA(A:A))<>"")/COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A))&"")-
                (COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A))&"")=1))

    That means if, for example, value ABC is repeated 4 times and other have no duplicates, formula returns 1 since that's only one value has duplicates.

     

    In your case it could be simplified to

    =SUMPRODUCT(1/COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A)))-(COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A)))=1))

    assuming you have no blank cells within the range.

     

    If you have ABC repeated 4 times and one XYZ, when

    total = 5

    with duplicates = 1

    count ignoring duplicates = 2

Anıl Adaş's avatar
Anıl Adaş
Brass Contributor
Apr 15, 2019

Actually I can easily find the number of dublicates like "total number of Column A - total number without dublicates" but is it possible to calculate doing opposite like "total number of Column A - dublicates"

 

Its hard to explain why but actually I exactly need to find a formula for D7 on yellow marked line on this example file.

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 15, 2019

Anıl Adaş , if calculate how many values have duplicates, that's

=SUMPRODUCT((A2:INDEX(A:A,COUNTA(A:A))<>"")/COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A))&"")-
            (COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A))&"")=1))

That means if, for example, value ABC is repeated 4 times and other have no duplicates, formula returns 1 since that's only one value has duplicates.

 

In your case it could be simplified to

=SUMPRODUCT(1/COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A)))-(COUNTIF(A2:INDEX(A:A,COUNTA(A:A)),A2:INDEX(A:A,COUNTA(A:A)))=1))

assuming you have no blank cells within the range.

 

If you have ABC repeated 4 times and one XYZ, when

total = 5

with duplicates = 1

count ignoring duplicates = 2

  • Anıl Adaş's avatar
    Anıl Adaş
    Brass Contributor
    Apr 16, 2019

    SergeiBaklan 

     

    Hi sergei, your formula was very helpful but I wanna ask you one more thing about this case. If I add a new column and say X to every row, and after than add some Y is it possible to make this calculation for A and B separately like attached example. If I keep continue to use your formula, its gonna calculate for all numbers in column B and cannot separate X and Y. I basicly tried to add an IF to make calculation separately but it didn't work.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Apr 16, 2019

      Anıl Adaş ,

       

      total

      =COUNTIF($A2:INDEX($A:$A,COUNTA($B:$B)),$E$4)

      with duplicates

      =SUMPRODUCT(($A2:INDEX($A:$A,COUNTA($B:$B))=$E$4)/
          COUNTIFS($B2:INDEX($B:$B,COUNTA($B:$B)),
                             $B2:INDEX($B:$B,COUNTA($B:$B)),
                              $A2:INDEX($A:$A,COUNTA($B:$B)),
                              $A2:INDEX($A:$A,COUNTA($B:$B))
) -
(COUNTIFS($B2:INDEX($B:$B,COUNTA($B:$B)),
$B2:INDEX($B:$B,COUNTA($B:$B)),
$A2:INDEX($A:$A,COUNTA($B:$B)),
$E$4)*($A2:INDEX($A:$A,COUNTA($B:$B))=
$E$4)=1))

      without duplicates

      =SUMPRODUCT(($A2:INDEX($A:$A,COUNTA($B:$B))=$E$4)/
          COUNTIFS($B2:INDEX($B:$B,COUNTA($B:$B)),
                             $B2:INDEX($B:$B,COUNTA($B:$B)),
                              $A2:INDEX($A:$A,COUNTA($B:$B)),
                              $A2:INDEX($A:$A,COUNTA($B:$B))
))

Resources