Forum Discussion
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.
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
8 Replies
SergeiBaklan 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 :)
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.
