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...
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!
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.
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?
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?
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
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 😁
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?
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.
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.
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.
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
***** 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
***** 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
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?
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
Sounds like a potential video! :-) This would be done with a couple of steps. This video might give some insight ruclips.net/video/SUNfywPWi6o/видео.html
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?
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.
***** 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.
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...
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.
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.
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
I watched 5 videos before yours and was still like - "what?!?". Watched your video, spreadsheet calculated in minutes. Total life saver, thank you!
Hi Lilliea Jackson, glad you liked it, thanks for commenting!
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...
Awesome, this pivot process helped me address my challenge of finding out no. of duplicates in a column. Thanks a lot. DJ
VERY helpful. It took me a few views (that's how I roll) but eventually it sunk in! My heartfelt thanks!
Fantastic!
Thanks a lot, Doug. Your demonstration helped a lot with what I was trying to achieve in my spreadsheet!
Glad it was helpful!
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!
Hi Lari Anne Mazzitello, glad you liked it, thanks for commenting!
Just what I was looking for extremely clear and concise -Thank you !!
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. :)
Great solution! So well explained. Thank you!
Hi Patricia Gellon, you're welcome!
Terrific explanation. You´ve just won a new subscriber 👍
Thanks for the sub!!
I am so happy i came across your page !
Glad you liked!
thank you so much dough! this is very valuable and saved me a lot of time. more power to you and god bless!
Many thanks for explaining this clearly, very helpful.
Glad it was helpful!
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.
Hi Shane Gune, thanks for adding to the thread!
Thank you, simple and intuitive
Thanks Allan Park!
Thank's, you've saved lot's of my long working days and nights!
Thank you. Your videos are just amazing
Thanks for the tip. Very helpful.
Thank you
Glad it helped!
thank you so much.. the mentioned tutorial was useful for me.
Glad you found it useful!
Thank you Doug! I needed this. :)
Its Superb.....Thanks very much
Hi Royz Varghese, thanks for the comment!
Thanks for your information. This is solution.
Hi Wikarso Fahri...you're welcome, glad you liked!
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?
Hi Nenad Kralj, thanks for the comment!
Great Job Sir!!!
Thanks!
Extremely Helpful
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?
*****
Roger - thank you.
Thank you very much, That is exactly what I need. Very appreciate.
Hi hemseam...you're welcome, glad you liked!
Thank you Doug, this helped me a lot!
Thanks Mel Joseph, glad it helped!
Great video man..! Exactly the solution I needed. Thank you !
Hi Jayanti Viswanath, glad you liked it, thanks for commenting!
Thanks for this helped a lot
Thanks Mohau Kulima, glad it helped!
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
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.
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 😁
Thank you, you save my life
Thanks Gloria Esti Kurniati, glad it helped!
Thank you very much sir..............
Most welcome
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?
Power Query can probably do that
ruclips.net/user/dough517search?query=power+query+unique
How do you combine the two? Say Blank = 0, Unique = 2 and Duplicates = 3.
***** Thanks for your reply. Sorry I was't being very descriptive but I got it figured out. Thanks to your video :)
Would you know to teach me how to count duplicates and triples for 5 number drawn lotteries?
Congrats, that was very helpfull tanks a lot
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.
That would be the ALT key, so type press on the ALT and "=" keys
He actually said the "ALT Key" not "out key". Press the ALT Key and "=" key together and it autosums as a keyboard shortcut! BRILLIANT!
very smart , thank u
You're welcome!
actually i have been asked this question in interview and i didnt know the ans thanks to you now i know.
Hi ashish parmar, thanks for the comment!
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.
Might want to use the TEXT function to change it to text and then perform the lookup ruclips.net/video/w1ERp-eByus/видео.html
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.
Thanks! Very usefull well explain guide!
Thanks for the comment!
Thanks, Can you please guide us to do the same thing in MS Access?
You're Welcome! Sorry don't know Access a well...:-(
No issues Sir.. :-)
THANK YOU FOR THIS VIDEO.
You're Welcome!
Thank you, It was very helpfull
Thank you Doug.
You're Welcome!
Thank you Doug sir... I needed this. :)
You're welcome!
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
***** 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
***** Kool will do thanks for the link and all the help.
***** 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
Instead can we use highlight duplicates in home tab ?
Conditional Formatting is another option
ruclips.net/video/g_px2dGS5UE/видео.html
ruclips.net/video/meogoA_MXmg/видео.html
Thank you for this. i had repeated number for each year...
You’re welcome
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?
maybe some of the other videos can give some insight
ruclips.net/user/dough517search?query=count+duplicate
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
Sounds like a potential video! :-)
This would be done with a couple of steps. This video might give some insight ruclips.net/video/SUNfywPWi6o/видео.html
Thank you.
You're welcome!
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?
you might want to use the ISTEXT OR ISNUMBER function with this ruclips.net/video/oZnI0RcGQZk/видео.html
Sir I have a question
If I write a name in A row I want that name on D row how it's possible?
in row D, use =A1 (or whatever row number the value is in)
Life saver!!!!!
Thanks Luca Placidi, glad it helped!
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.
***** This doesn't work in Excel 2013
archipelago93
It does. I had to learn array functions (CTRL+SHIFT+ENTER) first, but the principle works in Excel 2013.
*****
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.
THANKS A LOT
You're Welcome!
i love you sir
i find it... thanks vrymuch sir..
Hi Marchelcello Studios, glad you liked it, thanks for commenting!
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...
if it were A2:A2, dragging the fill handled to copy down would give A3:A3, A4:A4, etc.
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.
maybe this vid will give an alternative solution for you ruclips.net/video/_0PIaScbLCY/видео.html
thanks a lot !!!!!!!!!!!
If I would like to add same instances than how we can sum all the same instances. Please reply
try the sumif or sumifs functions
ruclips.net/video/QxNfMjshvOk/видео.html
@@DougHExcel thank you dear.
thanks .. extreme level
Hi Dhaka Bangladesh, thanks for the comment!
Fantastic!!!
Thanks for the comment!
great, so how can i apply this count to a filtered column?
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
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.
Maybe something like this would give some insights
ruclips.net/video/mIL6rtJIUb4/видео.html
ruclips.net/video/sJqmqKBKwGs/видео.html
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.
Thank you
You're Welcome!
Thank you very much...:-)
You're welcome!
thank you 😭
You’re welcome!
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
Hi Daashka Daashka, sorry 🙁....but try a post on the mrexcel.com forum!
Sir, how unique subtotal ?
Sorry, don’t understand the comment/ question...
Thank you Sir
You're Welcome!
How do I do these formulas with a filtered list?
Hi Kellyn Rivero, sorry I don't do consulting :-( ....but try a post on the mrexcel.com forum!
this is awesome
Hi Shubham Patel, glad you liked it, thanks for commenting!
you deserve this
Is there another way to duplicate
Sorry, don’t understand the comment/ question...
i dont know how I got here. i just woke up with autoplay on.
😆
WTF I CANT
Hi Mikooljohn, thanks for the comment!
Im using Ctrl + F and just counting all :( im fcking nub
Hi Mikooljohn, thanks for the comment!