Count duplicate values in excel | Using Formulas

In this video, we will see how to Count duplicate values in excel using a simple formula.
We will use the COUNTIF formula to quickly Count duplicate values in excel and show a frequency table.
Click here to subscribe to my channel to get more videos of Excel formula tips and uses:
/ @exceltipsvideos
Check out our Excel Graph Playlist for ideas on creating great-looking charts :
• Excel Graphs

[@cyjohntv4465]

thanks a lot sir

[@user-ki3wp6me9r]

Thank you So much.

[@ExcelTipsVideos]

Glad that I could help

[@noemymauricio7839]

Ive been 2 hours watching the same thing in anothers channels for a school project and i coudnt do it. Thanks with your video i did it in less than 4min.

[@ExcelTipsVideos]

Glad that this was helpful

[@pawankumarvashistha417]

Tahnks from BHARAT (India) !

[@mayur7673]

THANK YOU SO MUCH

[@ExcelTipsVideos]

You're welcome!

[@alexanderclarke2508]

Awesome video thank you 👍🏻

[@aymanmohamed221]

Thank you brother Very informative

[@anand12342]

very helpful
thanks

[@ExcelTipsVideos]

Thanks for your support

[@kimberlysvasquez6238]

Thank you! You saved me so much time :)

[@ngonimichaelmayeresera749]

best example ever

[@vishalbabu5975]

Thankyou bro, it's very helpful

[@shreyashh]

Thank you so much for this video.

[@hazimmohamed3217]

Thank you for my assignment easier..keep continue to give a useful knowledge in next video..Much lovee😇😇

[@edwarddizengoff8414]

Thank you so much this was very helpful and saved me so much time!

[@ExcelTipsVideos]

Glad it helped!

[@harish70782]

Thanks siir ji🙏

[@esunilkumar9548]

Thank you so much

[@sourabhmayekar3354]

Thank you. This saved me so much time

[@ExcelTipsVideos]

You're welcome!

[@chaudhryjunaidashraf2548]

Great To Watch, Thanks For Making

[@ExcelTipsVideos]

Glad that it was helpful

[@JayGreezy]

Thanks buddy.

[@ExcelTipsVideos]

Glad that I could help

[@lakshmisai8799]

Thank you very much for the great video, did my work very easily🤝

[@ExcelTipsVideos]

Glad it helped

[@chujacartoon1]

Very well explained. Thanks a lot.

[@ExcelTipsVideos]

Glad it was helpful!

[@Cherryb0nb0ns]

THANK YOU SO MUCH!!

[@ungkesuleh4137]

Thank you so much. So helping for me to make a schedule better.. +subbed..

[@ExcelTipsVideos]

Thanks for your support

[@yashshriwas4253]

Very very thanks to you ,it's a very valuable video

[@ExcelTipsVideos]

Glad to hear that… thanks

[@sunderpreetsaini]

VERY EASY WAY OF EXPLAINING.VERY INTERSTING.

[@ExcelTipsVideos]

Thanks for liking

[@R10ap]

This was soo helpful, thank you so much!!!

[@ExcelTipsVideos]

Glad that I could help

[@Jas_Hari]

Very helpful, thank you

[@sangeetavhatkar148]

THANK YOU SIR. YOU EXPLAINED SO EASY.

[@ExcelTipsVideos]

Glad that it helped

[@saidaismail9612]

so easy to follow! thanks

[@ExcelTipsVideos]

Thanks for your support

[@sarahahenkanboateng1792]

Thank you so much. This really has solved all my duplicates wahala. You are forever be blessed

[@ExcelTipsVideos]

Glad that it helped

[@prateekagrawal1989]

Thank you so much.

[@mariacas7974]

Great explanation!

[@ExcelTipsVideos]

Glad it was helpful!

[@prony5145]

Great thank you💓

[@sourabhsankpal9297]

thank you

[@ExcelTipsVideos]

You're welcome

[@nancyattoh8998]

Thank you, this is was so well explained and easy to follow.

[@ExcelTipsVideos]

Thanks

[@raedhalabi188]

Thank you its so clear

[@travismills5972]

Thank you!

[@sanketnimhan4470]

Very helpful thank you so much

[@ExcelTipsVideos]

Thanks for your support

[@sahanyakapugedara6770]

Ok thanks 😍😍😍

[@ExcelTipsVideos]

Glad that this was helpful

[@jeevanandhamvivekanandan8540]

Good one

[@ExcelTipsVideos]

Thank you for your support

[@youcantseeme1554]

Thank you so much...this helped me a lot ❤️

[@ExcelTipsVideos]

Glad that I could help

[@purnachandrapalai4188]

Thank you ❤

[@kanchanchatterjee3193]

Thank you! Nice video

[@ExcelTipsVideos]

Glad you liked it!

[@Amir-bq8pm]

I love you man ❤️

[@ExcelTipsVideos]

Glad that it helped

[@dr.roshenjohn1401]

such a great help this is

[@ExcelTipsVideos]

Thank you for your support

[@delossantosjoshual.1459]

Thank you for the tips! Now I can easily solve the mean😅

[@ExcelTipsVideos]

That is really a good use of this formula 😀😀😀

[@rachitajindal4064]

Thanks it was very helpful

[@ExcelTipsVideos]

Glad to hear that!

[@MrDhunpagla]

Neat & informative 👍

[@ExcelTipsVideos]

Glad it was helpful!

[@shahuljamal1410]

Super

[@shinnatangem]

Thanks a lot!!!
Are there anyways to convert the final result of this video back to a raw list because I've got a file which already count duplicates however I also needed a raw list too. So I need a way to convert the count duplicate list back to only duplicate list. I hope this question doesn't confused you thankyou

[@debasishmondal19]

Love it

[@ExcelTipsVideos]

Glad that it helped

[@ubaidillahmuhammad20]

useful trick. but please put all practiced files [.*xlsx] in the video description ....Thanks

[@ExcelTipsVideos]

Thanks for the suggestion. I will upload the files in Github and share the link in the Description

[@MrNaturalcure]

How to do this if the same data is in adjacent 8 columns ?
Thanks

[@moesadr3342]

Does anyone knows how to make this automated that the result will dinimically changes as we enter or edit the records and / or thier values?

[@sanalkumar6053]

I think it doesnt count the non repeated item as 1 . Count column cannot be 1 . Is there any way to solve this.

[@madhavipillai9108]

How to find duplicates comparing two sheets in one excel?? Please reply..

[@ExcelSmith]

Hello. The first step is to create a list of unique values like in column I in the video. The easiest way is to follow the same steps as in the video for the values in Sheet1. Then, copy the values from Sheet2 and paste them below the copied values from Sheet1. This will give you a long list containing all of the values from Sheet1 and Sheet2. Then, like in the video, use Remove Duplicates to get a list of unique values across both sheets.
To find how many times each of these values occur in both Sheet1 or in Sheet2, we can add two COUNTIF functions together. The first COUNTIF counts the unique values in the list of values in Sheet1. The second COUNTIF counts the unique values in the list in Sheet2.
For example: The range A2:A6 in Sheet1 contains the values A; B; C; B; C. The range A2:A6 in Sheet2 contains the values B; C; D; B; D. Create a list of unique values starting in cell C2 on Sheet1. The unique list would contain the values A; B; C; D. Entering =COUNTIF($A$2:$A$6,C2)+COUNTIF(Sheet2!$A$2:$A$6,C2) in cell D2, and dragging down, gives us the final result:
Unique Values Count Duplicates
A 1
B 4
C 3
D 2

[@mytechnosupport269]

Low volume problem ?

[@ExcelTipsVideos]

We were trying with a different audio setting....obviously did not work very well 😀

[@fathimahjasnawi3165]

mine is not working. Ask to remove the outline. Already remove the borders. Still not function😢

[@alimaa.taboure8133]

why does it keep saying : you can't change part of an array ?
Thank you

[@ExcelTipsVideos]

That error means you are trying to edit part of a array. You will need to select the entire array

[@arshkhan9606]

Very low volume

[@hussamkassoumeh7833]

work on your Audio man, I cant hear anything, so low audio recording, Check your video before posting
Thanks

[@ExcelTipsVideos]

Thanks for the feedback - I did have issues with the audio at that time - and I have rectified them in my later videos.

[@q-relax7737]

Thank you. Related to above, can you please help with this. Please see below data in column A, The desired result are in column B, how can we get the results in column B with formula.
Column A required output in Column B
Cell A1= Peter Cell B1=
Cell A2= John Cell B2=
Cell A3= Peter Cell B3= 1
Cell A4= David Cell B4=
Cell A5= Peter Cell B5=2
Cell A6= Blank cell Cell B6=
Cell A7= Peter Cell B7=
Cell A8= John Cell B8=
Cell A9= John Cell B9=1
Cell A10= John Cell B10=2
Cell A11= Maria Cell B11=
Cell A12= Blank cell Cell B12=
Cell A13= Maria Cell B13=
Cell A14= Maria Cell B14=1
Cell A15= Peter Cell B15=
Cell A16= David Cell B16=
Cell A17= Maria Cell B17=2
I have used =SUBSTITUTE((COUNTIF($A$1:A1,A1)-1),0,"") but it works fine only untill first blank cell in column A. In B7 this formula is giving 3, but it should be blank. Basically after every blank in Column A a new set of data develops. The requirement is to serial the duplicates between two blanks in column. Hope this explains the problem. Thank you. Much appreciated.

[@jesl.6263]

Thank you!

[@ExcelTipsVideos]

You're welcome!

[@lumion-rendering]

Very helpful thanks