Count Unique or Duplicate Values in a List

Подписаться 79 тыс.

Просмотров 330 тыс.

50% 1

Have you ever had a friend yelled random numbers at you while you're counting values? Aside from wanting to toss your friend in a lake you'd like to have an easier way to count these numbers to tell which are unique and which are duplicates. If the list is small that not a probably to do this type of analysis. If the list is big then forget trying to do it in your mind. Why not just use Excel? It's basically a sophisticated counting application. There's actually a function in Excel called COUNTIF and it counts stuff that meets a criteria. Check our the video below to see how's it all done.
🔔 SUBSCRIBE to my channel ➜ goo.gl/wN3c3p
🏫 Excel Training ➜ www.exceltrain....
📚 Excel Books & Tech Gear ➜ www.amazon.com...
⚙️ Tools: Screencasting ➜ techsmith.z6rj...
⚙️ Tools: Microsoft Office ➜ microsoft.msaf...
⚙️ Tools: TubeBuddy ➜ www.tubebuddy....
📝 This description may contain affiliate links and we'll receive a small commission if a purchased is made using the links (but at no additional cost to you). It'll support the channel and so more videos like this can be made. Thanks for your support!
🎁 If you find these videos useful and want to support my channel go to www.buymeacoff...
#excel
#msexcel
#doughexcel
~-~~-~~~-~~-~
Please watch: "Convert Table in a PDF File to Excel"
• Convert Table in a PDF...
~-~~-~~~-~~-~

Опубликовано:

9 сен 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 158

@lillieajackson9309 6 лет назад

I watched 5 videos before yours and was still like - "what?!?". Watched your video, spreadsheet calculated in minutes. Total life saver, thank you!

@DougHExcel 6 лет назад

Hi Lilliea Jackson, glad you liked it, thanks for commenting!

@donpierce4176 10 лет назад

Doug this is AMAZING for two weeks I was trying to know how to convert a text "number" to a real Number for further calculations; because the numbers I was geting was from a mid(Formula) which looked like a number but was a text. This I could not find anywhere. Your explanation are clear to the point and simple,Thank You Doug . If you got training things on excel especially formulars utilising IF function I am happy to pay for it...

@shanegune1545 5 лет назад

Thanks alot for the sharing. I just successful in placing the formula. Which was my real goal. Here is in text format if someone difficult to go through video. =COUNTIF($B$1:B1,B1) B is column name and 1 is the cell number.

@DougHExcel 5 лет назад

Hi Shane Gune, thanks for adding to the thread!

@lamazz03 6 лет назад

This video was FANTASTIC! It is the only one I've seen that stepped you through the logic of the formulas for the counting of unique/duplicate entries. Now that I understand how the formula works, I can use in so many applications! Just knowing the formula doesn't allow you to tweak it for different circumstances. This was fantastic!!! As an additional bonus, he saved me many hours of time with his keyboard short cuts and editing multiple formulas at once! Not to mention the double clicking to expand the formula down the column. This video is a true value! Thank you for taking the time to post it!

@DougHExcel 6 лет назад

Hi Lari Anne Mazzitello, glad you liked it, thanks for commenting!

@mdhanunjayareddy1029 9 лет назад

Awesome, this pivot process helped me address my challenge of finding out no. of duplicates in a column. Thanks a lot. DJ

@DouglasBorses 11 лет назад

Just what I was looking for extremely clear and concise -Thank you !!

@heyitsscubasteve 2 года назад

Thanks a lot, Doug. Your demonstration helped a lot with what I was trying to achieve in my spreadsheet!

@DougHExcel 2 года назад

Glad it was helpful!

@PatriciaGellon Год назад

Great solution! So well explained. Thank you!

@DougHExcel Год назад

Hi Patricia Gellon, you're welcome!

@earlymorningproductions9355 4 года назад

VERY helpful. It took me a few views (that's how I roll) but eventually it sunk in! My heartfelt thanks!

@DougHExcel 4 года назад

Fantastic!

@astonduncan1263 2 года назад

I am so happy i came across your page !

@DougHExcel 2 года назад

Glad you liked!

@jaog4462 2 года назад

Terrific explanation. You´ve just won a new subscriber 👍

@DougHExcel 2 года назад

Thanks for the sub!!

@ColinMacIver 3 года назад

Many thanks for explaining this clearly, very helpful.

@DougHExcel 3 года назад

Glad it was helpful!

@cpatricio1 10 лет назад

thank you so much dough! this is very valuable and saved me a lot of time. more power to you and god bless!

@mlsauron 10 лет назад

Thank's, you've saved lot's of my long working days and nights!

@allanpark6046 4 года назад

Thank you, simple and intuitive

@DougHExcel 4 года назад

Thanks Allan Park!

@Draconarious 4 года назад

What if I want Column E to automatically list all unique values in Column A as they are added and removed, and for Column F to to take the Unique Value designated to its left and count the number of instances where that value appears at any point in Column A?

@DougHExcel 4 года назад

Power Query can probably do that ru-vid.comsearch?query=power+query+unique

@gloriaestikurniati6410 4 года назад

Thank you, you save my life

@DougHExcel 4 года назад

Thanks Gloria Esti Kurniati, glad it helped!

@tkdburnout 10 лет назад

GREAT GREAT GREAT VIDEO, you got a new subsriber ^^ I was looking for a while to find the good formula and you made it happen. :)

@meowphillic 9 лет назад

Thank you. Your videos are just amazing

@EduardoPiston 9 лет назад

Would you know to teach me how to count duplicates and triples for 5 number drawn lotteries?

@WikarsoFahri 5 лет назад

Thanks for your information. This is solution.

@DougHExcel 5 лет назад

Hi Wikarso Fahri...you're welcome, glad you liked!

@hanalei412002 9 лет назад

Hi, I am wondering if you know how to exclude strikethrough letters in a countif formula? At this time I am using =COUNTIF(E11:E37,"*B*") but it is still picking up my strikethrough letters which is giving me an in accurate value. Do you know how to exclude any type of strickthrough letters? thanks

@hanalei412002 9 лет назад

***** Thanks for that, I have just reg. with them I'm waiting my approval so I can ask the question. Theres on post that talks a bit about it but not really what I am looking for. he called it =countnostrikethrough(E11:E37,"*A*") but when I tried it, this didn't work for me I maybe missing something. thanks again. www.mrexcel.com/forum/excel-questions/851864-excel-formula-excluding-strikethrough.html Excel formula for excluding strikethrough

@hanalei412002 9 лет назад

***** Kool will do thanks for the link and all the help.

@hanalei412002 9 лет назад

***** Ok here it is, I did what you said and they got back to me with this function: nonstrikethroughs. thanks for your help maybe you might want to do a video on this because it's no where to be found on forms or on youtube. Function StrikeThroughs(Rng As Range, Letter As String) As Long Dim X As Long, ST As Long, Cell As Range Application.Volatile For Each Cell In Rng For X = 1 To Len(Cell.Value) If Mid(Cell.Value, X, 1) = Letter Then StrikeThroughs = StrikeThroughs - Cell.Characters(X, 1).Font.Strikethrough End If Next Next End FunctionFunction NonStrikeThroughs(Rng As Range, Letter As String) As Long Dim X As Long, ST As Long, Cell As Range Application.Volatile For Each Cell In Rng For X = 1 To Len(Cell.Value) If Mid(Cell.Value, X, 1) = Letter Then NonStrikeThroughs = NonStrikeThroughs - (Not Cell.Characters(X, 1).Font.Strikethrough) End If Next Next End FunctionFunction CellsWithStringStrikeThroughs(Rng As Range, Letter As String) As Long Dim X As Long, ST As Long, Cell As Range Application.Volatile For Each Cell In Rng For X = 1 To Len(Cell.Value) If Mid(Cell.Value, X, 1) = Letter And Cell.Characters(X, 1).Font.Strikethrough Then CellsWithStringStrikeThroughs = CellsWithStringStrikeThroughs + 1 Exit For End If Next Next End Function

@andrewtaylor8755 2 года назад

Great Job Sir!!!

@DougHExcel 2 года назад

Thanks!

@Anandraj-is1yd 4 года назад

Thank you very much sir..............

@DougHExcel 4 года назад

Most welcome

@gowdablr 9 лет назад

Thanks for the tip. Very helpful.

@mikehdz9606 5 лет назад

I have data running from B7 to R60, in which I need only a count of numbers only, but the fields contain duplicate numbers and text as well... I need to count only one of the duplicates and no text... So I ended up with =SUMPRODUCT((B7:R60"")/COUNTIF(B7:R60,B7:R60&"")) , but this is counting text as well, what criteria should I use for the formula not to count text and only numbers?

@DougHExcel 5 лет назад

you might want to use the ISTEXT OR ISNUMBER function with this ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-oZnI0RcGQZk.html

@multicreations3640 4 года назад

Sir I have a question If I write a name in A row I want that name on D row how it's possible?

@DougHExcel 4 года назад

in row D, use =A1 (or whatever row number the value is in)

@royzvarghese4707 5 лет назад

Its Superb.....Thanks very much

@DougHExcel 5 лет назад

Hi Royz Varghese, thanks for the comment!

@tariqshahuk 9 лет назад

hi Thanks for sharing, just want to know how to count total occurrence of 5, 8 , or 2 in the table. thanking you in advance.

@mirrrvelll5164 3 года назад

I still didn't get it why is necessary to absolute on first $A$2:A ?,,,,, when I did without, I see no difference. I mean, what is purpose of it? It selects my range only...

@DougHExcel 3 года назад

if it were A2:A2, dragging the fill handled to copy down would give A3:A3, A4:A4, etc.

@gopi-ep6mb 4 года назад

Instead can we use highlight duplicates in home tab ?

@DougHExcel 4 года назад

Conditional Formatting is another option ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-g_px2dGS5UE.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-meogoA_MXmg.html

@preethiganesan8789 8 лет назад

thank you so much.. the mentioned tutorial was useful for me.

@DougHExcel 8 лет назад

Glad you found it useful!

@Caro.travelandart 5 лет назад

Hi! thanks for the video. Could you please help me with the following concern? I have a list of series numbers, and I want to know how many duplicates are, but by using this formula, I would always be missing "the original" duplicate. What I mean is that in your excel, I would like for the "5" of cell A2 to be highlighted as well, since it is part of the duplicates. Is there any way to do that?

@DougHExcel 5 лет назад

maybe some of the other videos can give some insight ru-vid.comsearch?query=count+duplicate

@luthfi4164 2 года назад

for the unique part, instead of using if statement and sum, we can use count if again to count how many times number 1 show up right? minimize the way to count unique values 😁

@MrBassRenegade 9 лет назад

I'm looking to count duplicate values that are consecutive. For instance: 4 4 5 2 2 2 2 4 2 2 2 2 6 6 8 2 7 I want to count the number of times 2 repeats four times. I feel like it's similar to what you're doing in this video, but not exactly the same

@hemseamify 6 лет назад

Thank you very much, That is exactly what I need. Very appreciate.

@DougHExcel 6 лет назад

Hi hemseam...you're welcome, glad you liked!

@calebbrooks5055 11 лет назад

Extremely Helpful

@elektralightepikk 8 лет назад

Thank you Doug, this helped me a lot!

@DougHExcel 3 года назад

Thanks Mel Joseph, glad it helped!

@NenadKralj 7 лет назад

That is exactly what I needed! I have repeating #text in #excel #cell Additionally - How to put a #sum into also? Like a 3 #formulas in one; all the above + sum?

@DougHExcel 3 года назад

Hi Nenad Kralj, thanks for the comment!

@jayantiviswanath9785 6 лет назад

Great video man..! Exactly the solution I needed. Thank you !

@DougHExcel 6 лет назад

Hi Jayanti Viswanath, glad you liked it, thanks for commenting!

@user-vt8qj9no5e 8 лет назад

Thanks! Very usefull well explain guide!

@DougHExcel 8 лет назад

Thanks for the comment!

@joselicop872 8 лет назад

Thank you Doug! I needed this. :)

@mohau987 5 лет назад

Thanks for this helped a lot

@DougHExcel 5 лет назад

Thanks Mohau Kulima, glad it helped!

@rishiksarkar62 5 лет назад

THANKS A LOT

@DougHExcel 5 лет назад

You're Welcome!

@AmanVerma-lo2mq 7 лет назад

Hi, i have some data in the Excel sheet and there is also a duplicates but i want count of the same data after removing the same.

@DougHExcel 7 лет назад

maybe this vid will give an alternative solution for you ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-_0PIaScbLCY.html

@peetm7 9 лет назад

Is it possible to put it into one formula - make one counter in one cell, just set the range? I can't add a column for the if(countif) formulas and I can't write a macro.

@archipelago9376 9 лет назад

***** This doesn't work in Excel 2013

@peetm7 9 лет назад

archipelago93 It does. I had to learn array functions (CTRL+SHIFT+ENTER) first, but the principle works in Excel 2013.

@peetm7 9 лет назад

***** Thanx, took me some time, I learn array formulas, adapted the principle for my needs, my formula barely fits to my screen, but works nicely. Thanx mate.

@21crazz 9 лет назад

How do you combine the two? Say Blank = 0, Unique = 2 and Duplicates = 3.

@21crazz 9 лет назад

***** Thanks for your reply. Sorry I was't being very descriptive but I got it figured out. Thanks to your video :)

@danielromero432 11 лет назад

Congrats, that was very helpfull tanks a lot

@excelengineer9143 3 года назад

very smart , thank u

@DougHExcel 3 года назад

You're welcome!

@figmauser1996 4 года назад

Thank you.

@DougHExcel 4 года назад

You're welcome!

@danielosorio217 6 лет назад

Doug H, thanks very much for posting this tips. A quick question: @ 5:07 you said to use the 'out key' press the = sign as a keyboard short cut to bring the SUM function. What is the 'out key' that we need to press along with the = sign to bring the SUM function? Thank you again.

@DougHExcel 6 лет назад

That would be the ALT key, so type press on the ALT and "=" keys

@lamazz03 6 лет назад

He actually said the "ALT Key" not "out key". Press the ALT Key and "=" key together and it autosums as a keyboard shortcut! BRILLIANT!

@amitkumar060189 7 лет назад

Is it possible to get result in a cell with single formula without helping column...........Like i want to count duplicate value from range then sum of the total duplicate with using Count-if and sum formula.

@DougHExcel 7 лет назад

Maybe something like this would give some insights ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-mIL6rtJIUb4.html ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-sJqmqKBKwGs.html

@amitkumar060189 7 лет назад

No, I am looking to get result through formula only in a single cell without helping column and without pivot table. Like : Both condition should met under a formula, 1st it should count duplicate and then add all duplicate. Like : =IF(COUNTIF($A$2:$A2,A2)>1,1,"") this formula will count duplicate value from range.....and now how to sum formula under above to add all duplicate value.

@q-relax7737 11 месяцев назад

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.

@luislizarraga2379 11 лет назад

Thank you, It was very helpfull

@noeldoyle2477 5 лет назад

Doug I am finding your videos really useful and they are saving me time and giving me some real advantages. I have a question about counting occurrences of date stamps In a sheet column Their are a 1000 rows, Column A is time stamp which varies some thing like 20/05/2019 08:10:55 If I wanted to count how many times a certain date occurs say 22/05/2019 (time being ignored) how would I do that.

@DougHExcel 5 лет назад

Might want to use the TEXT function to change it to text and then perform the lookup ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-w1ERp-eByus.html

@Atiks_Hassan_Studios 5 лет назад

thanks .. extreme level

@DougHExcel 5 лет назад

Hi Dhaka Bangladesh, thanks for the comment!

@lucaplacidichannel 4 года назад

Life saver!!!!!

@DougHExcel 4 года назад

Thanks Luca Placidi, glad it helped!

@Bullishtrader1182 5 лет назад

If I would like to add same instances than how we can sum all the same instances. Please reply

@DougHExcel 5 лет назад

try the sumif or sumifs functions ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-QxNfMjshvOk.html

@Bullishtrader1182 5 лет назад

@@DougHExcel thank you dear.

@ap5293 6 лет назад

actually i have been asked this question in interview and i didnt know the ans thanks to you now i know.

@DougHExcel 6 лет назад

Hi ashish parmar, thanks for the comment!

@DARIUSHAZIMI 7 лет назад

Thank you Doug.

@DougHExcel 7 лет назад

You're Welcome!

@Dxb-ex3eq 6 лет назад

THANK YOU FOR THIS VIDEO.

@DougHExcel 6 лет назад

You're Welcome!

@preethiganesan8789 8 лет назад

Hi Dough, ur videos are Great. Do you have any videos for segregating the unique and duplicate values? I mean the duplicate values have to be placed in a separate column and unique values separately in a column. Example: Names Unique Duplicates Sam Sam Sam Oliver Oliver Sara Sam Sara Sara Ben Ben Sam

@DougHExcel 8 лет назад

Sounds like a potential video! :-) This would be done with a couple of steps. This video might give some insight ru-vid.com/video/%D0%B2%D0%B8%D0%B4%D0%B5%D0%BE-SUNfywPWi6o.html

@dianab4067 4 года назад

Thank you

@DougHExcel 4 года назад

Glad it helped!

@shadowhaven012 6 лет назад

great, so how can i apply this count to a filtered column?

@DougHExcel 4 года назад

quickest is the copy only the unfiltered range and paste to new range or if you have O365 subscription the combo of the New Unique function

@bammbamm12 9 лет назад

I have 20,000 names on a list, most of which are listed more than once. Can this little exercise really help me count how many times each name is entered?

@bammbamm12 9 лет назад

***** Roger - thank you.

@wintapang9013 10 лет назад

thanks a lot !!!!!!!!!!!

@venkatvns2741 6 лет назад

Thank you Doug sir... I needed this. :)

@DougHExcel 6 лет назад

You're welcome!

@daashkadaashka248 8 лет назад

Hi Dough, i have five columns and so many rows data.first column is date/20160320,20160320,20160321 etc/, second is name/sarah, george etc/,third is sums. How can i remove dublicate and count it? EX data: 20160320 Sarah 2$ shokolad 20160320 Sarah 1$ sweet I need this: Date Name Sum Count 20160320 Sarah 3 1

@DougHExcel 3 года назад

Hi Daashka Daashka, sorry 🙁....but try a post on the mrexcel.com forum!