For some older excel versions, make sure you use Ctr+Shift+Enter to confirm the formula. This allows to run it as a CSE function which gives array results for COUNTIF instead of a single number result :)
You are the one who made me know Microsoft Excel, I bought your course on Udemy and since then I have been teaching Excel as well. You make Excel look so simple, especially the complex topics! Thanks so much my Tutor!
You have SUPERB teaching skills. Crystal clear communication, awesome! Subscribed because of that. So important to teach the complexities of things but also at the same time draw laymen references to it, makes it easy for everyone to understand.
Works like a charm! But I have no idea how it works (especially COUNTIF). I've made names of course with relative references and all is ok! Thank you very much!
@@OfficeNewb Instead of range A1:A5 I;ve created named range. Than I've created name with your formula and small procedure to adjust the range only. In each cell I have '= Unique' :)) With small IFERROR I have empty unused cells. You've made really greate code / trick whatever! Thanks! www.laban.rs/files/Uniq.png
Thank you so much for teaching this, bit new to excel was struggling with this a lot! you made the process very simple with multiple options to choose from, thanks again! 👍
Hi I'm using office 2019 and it didn't work, and i check on my office 2021 and it work, do you have any suggestion? Because if i should use office 2021, my coworker cannot apply the change because they are using office 2019 to below
Thanks for these formulas. However I'm on Excel 2016 and using the first solution, it first returned 0, and further I had alterning results in the list when pressing enter in a cell. So here is what I came with to make it work: -I entered my formula using Ctrl+shift+enter -I made sure to select the cell above the first result like in the video so it's not a circular reference ($L$1:L1 and not $L$2:L2) Note: here it's not the thing to do but in the process of solving the problem, I found out you can allow circular references by going in File>Option>Formulas, and check "iterative calculation"
The COUNTIFS function in Excel is typically used to count the number of cells that meet multiple criteria. It is not designed to create a unique list from a column of data with a specific number of duplicates.
I have subscribed to your udemy channel too. My doubt is if iam dumping data...suppose iam copying it from sap....duplicates are being copied to...how to prevent that
Here is a link from Microsoft that should be helpful. support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123
Here's my question. When using Unique - how do you turn that into a list you can manipulate? Because the values are in one cell you can't manipulate it like other data. I am trying to think of a way to take whatever is in the list and use that to populate a regular list that I can act further on.
In Excel, a circular reference occurs when a formula directly or indirectly refers to its own cell. This creates a loop where Excel cannot determine the value of the cell because it depends on its own value. Circular references can lead to errors and incorrect calculations in your spreadsheets. Excel is designed to detect and handle circular references in different ways, depending on your settings and the version of Excel you're using.
I am sorry to bother, but is there a way to extract unique list of values + how many times each of the values occured in the list? Edit: Sorry, I have already found it, thanks a lot for the video. :)
Great catch. You're right, because we don't know how many unique entries there are we don't know how many cells to drag. If you still want to use that formula, you can nest it within an IFERROR function. =IFERROR(INDEX($D$2:$D$445,MATCH(0,INDEX(COUNTIF($I$7:I7,$D$2:$D$445),0,0),0)), "GO BACK UP") If you drag too many, then you will get the message, "GO BACK UP"
WHAT PROBLEM WITH MY EXCEL IS : WHENEVER I TRIED THIS FORMULA IN A SEPARATE SHEET, THE RESULTS SHOWS "ZERO" WHERE AS THE FORMULA RUNS SMOOTHLY IN THE SAME SHEET OF DATA.
For some older excel versions, make sure you use Ctr+Shift+Enter to confirm the formula. This allows to run it as a CSE function which gives array results for COUNTIF instead of a single number result :)
Thank you for sharing!
Get this comment to the top lol! I was so frustrated when I had the formula exactly the same and got N/A.
You are the one who made me know Microsoft Excel, I bought your course on Udemy and since then I have been teaching Excel as well. You make Excel look so simple, especially the complex topics! Thanks so much my Tutor!
Wow, thanks!
@@OfficeNewb
You're welcome
More thanks to you.
You made so much impact in my life, you made me an Excel pro.
I hold you in high regard!
Love this lecturer. I've never seen the index,match and countif functions used together in this way.
You have SUPERB teaching skills. Crystal clear communication, awesome! Subscribed because of that. So important to teach the complexities of things but also at the same time draw laymen references to it, makes it easy for everyone to understand.
Wow, thank you!
Finally I found someone that explains this issue. Great job.
Glad it helped
Love the Index/Match function, thank you!!!
They are great functions!
Thank you, thank you, THANK YOU for making it so easy and showing different ways of getting the results!
You are so welcome!
An excellent presentation. Even though I have 365 and can use the Unique function, I was glad to practice all 4 methods.
Glad you enjoyed it. There's something to take away from all 4 solutions.
Thank you so much for this video. It is exactly what I was looking for.
Glad it was helpful!
Great information, exactly what I needed. These are some great tips. Thank you VERY Much.
Glad it was helpful!
Works like a charm! But I have no idea how it works (especially COUNTIF). I've made names of course with relative references and all is ok! Thank you very much!
Glad It works! Thank you!
@@OfficeNewb Instead of range A1:A5 I;ve created named range. Than I've created name with your formula and small procedure to adjust the range only. In each cell I have '= Unique' :)) With small IFERROR I have empty unused cells. You've made really greate code / trick whatever! Thanks! www.laban.rs/files/Uniq.png
Thank you this was very helpful!
Glad it was helpful!
Excelente Kyle, eres grande!!!. Saludos desde México.
thanks ,,,this help me on my excell journey
You are most welcome!
Thank you so much for teaching this, bit new to excel was struggling with this a lot! you made the process very simple with multiple options to choose from, thanks again! 👍
Glad it helped!
YOU ARE AMAZING THANK YOU SO MUCH FOR SHARING THIS!!!!!
Glad you enjoyed it!
Hi I'm using office 2019 and it didn't work, and i check on my office 2021 and it work, do you have any suggestion?
Because if i should use office 2021, my coworker cannot apply the change because they are using office 2019 to below
Hi it fixed, just when you input the formula just hit ctrl + shift + enter instead enter
@@Nofanizationsame issue and fix.
Thanks for these formulas. However I'm on Excel 2016 and using the first solution, it first returned 0, and further I had alterning results in the list when pressing enter in a cell.
So here is what I came with to make it work:
-I entered my formula using Ctrl+shift+enter
-I made sure to select the cell above the first result like in the video so it's not a circular reference ($L$1:L1 and not $L$2:L2)
Note: here it's not the thing to do but in the process of solving the problem, I found out you can allow circular references by going in File>Option>Formulas, and check "iterative calculation"
Thank you for sharing!
Genius!!!
Thanks
You're welcome!
Super sir.. I really appreciate you..
THANKS A LOT
You're welcome!
Is there any way to use a countifS function instead. For example I want a unique list of sales people that appear more than 5 times in the list?
The COUNTIFS function in Excel is typically used to count the number of cells that meet multiple criteria. It is not designed to create a unique list from a column of data with a specific number of duplicates.
I have subscribed to your udemy channel too. My doubt is if iam dumping data...suppose iam copying it from sap....duplicates are being copied to...how to prevent that
What do I do if I dont want a dropdown? I need a list with each persons sales.
You can use a function like SUMIF
It appears you have cell L1 referencing itself thus a pop up error of circular reference... How do I fix this?
Here is a link from Microsoft that should be helpful.
support.microsoft.com/en-us/office/remove-or-allow-a-circular-reference-8540bd0f-6e97-4483-bcf7-1b49cd50d123
I do have the same problem on excel 2016 and it returns 0. I'm checking the link above
Here's my question. When using Unique - how do you turn that into a list you can manipulate? Because the values are in one cell you can't manipulate it like other data. I am trying to think of a way to take whatever is in the list and use that to populate a regular list that I can act further on.
You can always copy the list and paste them as Values, so you just have the list with no formula.
Hi! Tried your formula on the sheets I am working, but got an error message: #REF! How can I fix it? Thanks.
Can you upload a screenshot so we can see your formula and what you are seeing?
would be handy if you pasted the formula's in description
Thank you for the suggestion. Will take this into consideration.
I am using the index formula in a program called Smartsheet and I am receiving a “circular reference” error. What would be the reason for this?
In Excel, a circular reference occurs when a formula directly or indirectly refers to its own cell. This creates a loop where Excel cannot determine the value of the cell because it depends on its own value. Circular references can lead to errors and incorrect calculations in your spreadsheets. Excel is designed to detect and handle circular references in different ways, depending on your settings and the version of Excel you're using.
@@OfficeNewb why did you not get the error when referencing the cell that the data was populating into?
I am sorry to bother, but is there a way to extract unique list of values + how many times each of the values occured in the list?
Edit: Sorry, I have already found it, thanks a lot for the video. :)
Great thank you for the update
but the index/match way has a downside right? you don't know how many cells you have to drag the formula down
Great catch. You're right, because we don't know how many unique entries there are we don't know how many cells to drag. If you still want to use that formula, you can nest it within an IFERROR function.
=IFERROR(INDEX($D$2:$D$445,MATCH(0,INDEX(COUNTIF($I$7:I7,$D$2:$D$445),0,0),0)), "GO BACK UP")
If you drag too many, then you will get the message, "GO BACK UP"
Nice beard. You look like the offspring off a Leprechaun and lumberjack. Jokes aside. Thanks for this tip.
WHAT PROBLEM WITH MY EXCEL IS : WHENEVER I TRIED THIS FORMULA IN A SEPARATE SHEET, THE RESULTS SHOWS "ZERO" WHERE AS THE FORMULA RUNS SMOOTHLY IN THE SAME SHEET OF DATA.
Unfortunately, I cannot help you if I cannot see your data or formula.
@@OfficeNewb how to send the data for analysis
Nice beard Kyle
Thank you
Thank you, this was very helpful!