SOLVED
Home

Remove Dublicates and Count (help pls)

%3CLINGO-SUB%20id%3D%22lingo-sub-453052%22%20slang%3D%22en-US%22%3ERemove%20Dublicates%20and%20Count%20(help%20pls)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-453052%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20column%20and%20I%20simply%20need%20to%20count%20them%20with%20the%20help%20of%20an%20excel%20formula.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EI%20add%20an%20example%20excel%20file%3C%2FSTRONG%3E%20to%20explain%20case%20easy.%20There%20are%20some%20numbers%20which%20I%20cannot%20remove%20but%20also%20cannot%20count%20them.%20I%20need%20a%20formula%20%3CSTRONG%3Efor%20D2%26nbsp%3B%3C%2FSTRONG%3EI%20can%20see%3CSTRONG%3E%20automatically%20the%20number%20of%20rows%20wihout%20dublicates%3C%2FSTRONG%3E%20which%20is%20%3CSTRONG%3E44%20at%20this%20example.%20%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20me%20if%20you%20know%20a%20way%20to%20do%20that.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-453052%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-453202%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20Dublicates%20and%20Count%20(help%20pls)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-453202%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F161700%22%20target%3D%22_blank%22%3E%40An%C4%B1l%20Ada%C5%9F%3C%2FA%3E%26nbsp%3B%2C%20as%20variant%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(1%2FCOUNTIF(A2%3AA46%2CA2%3AA46))%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-453553%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20Dublicates%20and%20Count%20(help%20pls)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-453553%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BHi%2C%20thanks%20your%20response%20first.%20Your%20formula%20is%20very%20helpful%20but%20I%20must%20change%20A2%3AA46%20in%20formula%20every%20time.%20If%20I%20have%20more%20number%20in%20column%20A%2C%20I%20have%20to%20change%20formula%20like%20A2%3AA900%3BA2%3AA900%20for%20900%20number.%20I%20need%20a%20constant%20formula%20for%20every%20time%20usage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20someone%20have%20an%20idea%20for%20this%20pls%20let%20me%20know%20%3A)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-453592%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20Dublicates%20and%20Count%20(help%20pls)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-453592%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F161700%22%20target%3D%22_blank%22%3E%40An%C4%B1l%20Ada%C5%9F%3C%2FA%3E%26nbsp%3B%2C%20you%20may%20use%20dynamic%20range%20like%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(1%2FCOUNTIF(A2%3AINDEX(A%3AA%2CCOUNTA(A%3AA))%2CA2%3AINDEX(A%3AA%2CCOUNTA(A%3AA))))%3C%2FPRE%3E%0A%3CP%3Eassuming%20you%20have%20no%20other%20data%20below%20in%20your%20column.%20Formula%20is%20in%20F2%20attached.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-453649%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20Dublicates%20and%20Count%20(help%20pls)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-453649%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThanks%20a%20lot%20for%20your%20formula%20its%20very%20useful.%20I%20have%20one%20more%20question%20about%20this%20case.%20Do%20you%20know%20a%20way%20to%20find%20how%20much%20dublicate%20I%20have%20in%20column%20A%20as%20I%20marked%20green%20at%20attached%20example.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-453698%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20Dublicates%20and%20Count%20(help%20pls)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-453698%22%20slang%3D%22en-US%22%3E%3CP%3EActually%20I%20can%20easily%20find%20the%20number%20of%20dublicates%20like%26nbsp%3B%22total%20number%20of%20%3CSTRONG%3EColumn%20A%20-%20total%20number%20without%20dublicates%22%3C%2FSTRONG%3E%20but%20is%20it%20possible%20to%20calculate%20doing%26nbsp%3Bopposite%20like%20%3CSTRONG%3E%22total%20number%20of%20Column%20A%20-%20dublicates%22%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIts%20hard%20to%20explain%20why%20but%20actually%20I%20exactly%20need%20to%20find%20a%20formula%20%3CSTRONG%3Efor%20D7%3C%2FSTRONG%3E%20on%20yellow%20marked%20line%26nbsp%3Bon%20this%20example%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-453838%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20Dublicates%20and%20Count%20(help%20pls)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-453838%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F161700%22%20target%3D%22_blank%22%3E%40An%C4%B1l%20Ada%C5%9F%3C%2FA%3E%26nbsp%3B%2C%20if%20calculate%20how%20many%20values%20have%20duplicates%2C%20that's%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((A2%3AINDEX(A%3AA%2CCOUNTA(A%3AA))%26lt%3B%26gt%3B%22%22)%2FCOUNTIF(A2%3AINDEX(A%3AA%2CCOUNTA(A%3AA))%2CA2%3AINDEX(A%3AA%2CCOUNTA(A%3AA))%26amp%3B%22%22)-%3CBR%20%2F%3E%20%20%20%20%20%20%20%20%20%20%20%20(COUNTIF(A2%3AINDEX(A%3AA%2CCOUNTA(A%3AA))%2CA2%3AINDEX(A%3AA%2CCOUNTA(A%3AA))%26amp%3B%22%22)%3D1))%3C%2FPRE%3E%0A%3CP%3EThat%20means%20if%2C%20for%20example%2C%20value%20ABC%20is%20repeated%204%20times%20and%20other%20have%20no%20duplicates%2C%20formula%20returns%201%20since%20that's%20only%20one%20value%20has%20duplicates.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20your%20case%20it%20could%20be%20simplified%20to%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT(1%2FCOUNTIF(A2%3AINDEX(A%3AA%2CCOUNTA(A%3AA))%2CA2%3AINDEX(A%3AA%2CCOUNTA(A%3AA)))-(COUNTIF(A2%3AINDEX(A%3AA%2CCOUNTA(A%3AA))%2CA2%3AINDEX(A%3AA%2CCOUNTA(A%3AA)))%3D1))%3C%2FPRE%3E%0A%3CP%3Eassuming%20you%20have%20no%20blank%20cells%20within%20the%20range.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20ABC%20repeated%204%20times%20and%20one%20XYZ%2C%20when%3C%2FP%3E%0A%3CP%3Etotal%20%3D%205%3C%2FP%3E%0A%3CP%3Ewith%20duplicates%20%3D%201%3C%2FP%3E%0A%3CP%3Ecount%20ignoring%20duplicates%20%3D%202%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-458517%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20Dublicates%20and%20Count%20(help%20pls)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-458517%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20sergei%2C%20your%20formula%20was%20very%20helpful%20but%20I%20wanna%20ask%20you%20one%20more%20thing%20about%20this%20case.%20%3CSTRONG%3EIf%20I%20add%20a%20new%20column%20and%20say%20X%20to%20every%20row%3C%2FSTRONG%3E%2C%20and%20%3CSTRONG%3Eafter%20than%20add%20some%20Y%3C%2FSTRONG%3E%26nbsp%3Bis%20it%20possible%20to%20make%20this%20calculation%20%3CSTRONG%3Efor%20A%20and%20B%20separately%3C%2FSTRONG%3E%20like%20attached%20example.%20If%20I%20keep%20continue%20to%20use%20your%20formula%2C%20its%20gonna%20calculate%20for%20all%20numbers%20in%20column%20B%20and%20cannot%20separate%20X%20and%20Y.%20I%20basicly%20tried%20to%20add%20an%20IF%26nbsp%3Bto%20make%20calculation%20separately%20but%20it%20didn't%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-460868%22%20slang%3D%22en-US%22%3ERe%3A%20Remove%20Dublicates%20and%20Count%20(help%20pls)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-460868%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F161700%22%20target%3D%22_blank%22%3E%40An%C4%B1l%20Ada%C5%9F%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Etotal%3C%2FP%3E%0A%3CPRE%3E%3DCOUNTIF(%24A2%3AINDEX(%24A%3A%24A%2CCOUNTA(%24B%3A%24B))%2C%24E%244)%3C%2FPRE%3E%0A%3CP%3Ewith%20duplicates%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((%24A2%3AINDEX(%24A%3A%24A%2CCOUNTA(%24B%3A%24B))%3D%24E%244)%2F%0A%20%20%20%20%20%20%20%20%20%20COUNTIFS(%24B2%3AINDEX(%24B%3A%24B%2CCOUNTA(%24B%3A%24B))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%24B2%3AINDEX(%24B%3A%24B%2CCOUNTA(%24B%3A%24B))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%24A2%3AINDEX(%24A%3A%24A%2CCOUNTA(%24B%3A%24B))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%24A2%3AINDEX(%24A%3A%24A%2CCOUNTA(%24B%3A%24B))%0A)%20-%0A(COUNTIFS(%24B2%3AINDEX(%24B%3A%24B%2CCOUNTA(%24B%3A%24B))%2C%0A%24B2%3AINDEX(%24B%3A%24B%2CCOUNTA(%24B%3A%24B))%2C%0A%24A2%3AINDEX(%24A%3A%24A%2CCOUNTA(%24B%3A%24B))%2C%0A%24E%244)*(%24A2%3AINDEX(%24A%3A%24A%2CCOUNTA(%24B%3A%24B))%3D%0A%24E%244)%3D1))%3C%2FPRE%3E%0A%3CP%3Ewithout%20duplicates%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((%24A2%3AINDEX(%24A%3A%24A%2CCOUNTA(%24B%3A%24B))%3D%24E%244)%2F%0A%20%20%20%20%20%20%20%20%20%20COUNTIFS(%24B2%3AINDEX(%24B%3A%24B%2CCOUNTA(%24B%3A%24B))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%24B2%3AINDEX(%24B%3A%24B%2CCOUNTA(%24B%3A%24B))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%24A2%3AINDEX(%24A%3A%24A%2CCOUNTA(%24B%3A%24B))%2C%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%24A2%3AINDEX(%24A%3A%24A%2CCOUNTA(%24B%3A%24B))%0A))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Anıl Adaş
Contributor

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

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

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

 

@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 :)

@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.

 

@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.

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.

Solution

@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

@Sergei Baklan 

 

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.

@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))
))
Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies