Excel Magic Trick # 272: Count Unique Or Duplicate Records

See how to Count unique records and Duplicate records using the SUMPRODUCT, COUNTIF, COUNT, COUNTA, IF and SUM functions. See seven (6) different methods. See single formulas and helper column formulas that speed up calculations.

Unique values
List of unique values
Count unique items

Category:

Science & Technology

Tags:

• Excel
• Count
• unique
• records
• and
• Duplicate
• SUMPRODUCT
• COUNTIF
• excelisfun
• Mike
• Gel
• Highline
• Community
• College

[agermoune]

You have no idea how much the new column for uniq number was helpful to me.

Thank you so much for taking time to post this videa!

[ExcelIsFun]

You are welcome!

[jinu502]

Dear Excelisfun, I am your fan and a regular follower of your videos. Your videos have done wonders in my work life. Now I am teaching excel in my office and I have been recognized as Excel Guru means Mater of excel. You are the true reason for my success. I thank you very very much and wish you all the great things in your life. May God bless you with wealth, health and keep you safe and sound. May almighty fulfill your desires and keep you and your family very happy and prosperous forever.TC

[ExcelIsFun]

I am glad that the videos help and you have become the Excel Master!! That is why I put the videos out there! You can help the cause by buying my new book: "Slaying Excel Dragons" - only $20 at most online stores!! The DVD that goes with the book should be out in about 1 week available at mrexcel[dot]com

Thanks for your support, jinu502 !!

[mmesde]

hi there mr awesome dude..ever since you've been a great help to me..i have boasted all my formulas in my work and its all because of you..i hope to learn more..by the way, here in this video, what formula should i use in order to count the unique value and then reflect their corresponding value..

clmn1 clmn2

3 25

3 25

3 25

i need to reflect this.... (clmn1)1 and (clmn2) 25

thanks a lot!!!

[ExcelIsFun]

I have a video that shows how to do formula (very complex):

Excel Magic Trick 473: Extract Unique Records with Formula (Complex Array Formula)

or the super easy way:

Excel Magic Trick 660: Advanced Filter Unique Records Only

The formula is when you need the process to dynamic.

The Advanced Filter method is for when you do it just once.

This video has even more detail about the formula (if you are interested):

Excel Magic Trick 627: FREQUENCY Array Function (10 Examples)

[m5kaminsky]

As someone trying to learn excel, I've tried watching your tutorials just to get the information that I need to get my work tasks done. I find your videos hard to follow and too fast for a beginner. Using too many shortcuts makes it hard for me to process what happened and what you're doing.

[ExcelIsFun]

Wow! That is a hot tip! I do not know OpenOffice Calc too well and so I appreciate the hot tip!

[theyangist]

Thanks for this one and your many others. For those who might have difficulties with the first trick on OpenOffice Calc, the only difference is that the Countif(Array;Array&"") goes Countif(Array;""&Array) to avoid the division by zero error.