• 671K Members
• 3,895 Online
• 828K Conversations
SOLVED

Contributor

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 count them. I need a formula for D2 I can see automatically the number of rows wihout dublicates which is 44 at this example.

Please help me if you know a way to do that.

8 Replies
Highlighted

Re: Remove Dublicates and Count (help pls)

@Anıl Adaş , as variant that could be

`=SUMPRODUCT(1/COUNTIF(A2:A46,A2:A46))`

Highlighted

Re: Remove Dublicates and Count (help pls)

@Sergei Baklan Hi, thanks your response first. Your formula is very helpful but I must change A2:A46 in formula every time. If I have more number in column A, I have to change formula like A2:A900;A2:A900 for 900 number. I need a constant formula for every time usage.

If someone have an idea for this pls let me know :)

Highlighted

Re: Remove Dublicates and Count (help pls)

@Anıl Adaş , you may use dynamic range like

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

assuming you have no other data below in your column. Formula is in F2 attached.

Highlighted

Re: Remove Dublicates and Count (help pls)

@Sergei Baklan Thanks a lot for your formula its very useful. I have one more question about this case. Do you know a way to find how much dublicate I have in column A as I marked green at attached example.

Highlighted

Re: Remove Dublicates and Count (help pls)

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.

Highlighted
Solution

Re: Remove Dublicates and Count (help pls)

@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

Highlighted

Re: Remove Dublicates and Count (help pls)

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.

Highlighted

Re: Remove Dublicates and Count (help pls)

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))
))```