Took me longer than expected but finally got it right! Exercise 2, How many people have joined in the first 3 months of 2018 & 2019 (My solution): =FILTER(staff[Date Joined],(MONTH(staff[Date Joined])
True. Chandoo has been my sensei since 2016 and I always have to check on his videos. My daughters are starting to learn from him and I feel blessed. Thanks, Mr Chandoo
Hi, Have you used this formula for the last exercise =FILTER(sales,(sales[Sales Person]"Gunar Cockshoot")*(sales[Amount]>(AVERAGE(FILTER(sales[Amount],sales[Sales Person]="Gunar Cockshoot")))))
I watched and read many sources explaining FILTER and nothing else clicked. This video alone enabled me to create the spreadsheet I envisaged but didn't know how to achieve and I can't thank you enough!
Geez, a complete unknown formula for me! Great comprehensive and funny video! Can’t wait to be next morning to try this on my spreadsheets! Thank you for the discovery 😍
I saw this video earlier...but practically there was no requirement. Now I got a situation to use Filter...I immediately referred this video...thank you chandoo garu...u r excel God
Hi Chandoo you are such a great mentor for the people who wants to learn more & more advance techniques of data analytics, I have attended so many lectures of excel, Query and Power Query but I always end up my learning with your lectures only. I have gone through your video but i am feeling difficulty in exercise Sr. no. 2 & 5 (Completed). can you please share the formula for these exercise ?
It was just yesterday I discovered filter function, however I had no idea about all these amazing ways this function can be used. Thanks for great video Chandoo 👍
Please tell me i watched videos for beginners to intermediate level on this channel and here i didn't understand in last tricky formula in playlist videos aren't in sequence which one I'd watch?
Awesome teaching. Thank you for sharing your knowledge and experience. I am so thrilled to see and practice all these features in Excel. And I do them for sheer joy. Though I did my engg in civil, I have been interested in DA. Though I have joined - in the race a bit late, I am so grateful to utubers like you who are sharing their knowledge so freely to the world. More success and power to you... keep up the good work... 👍👍
Ha! Only yesterday I had to use the Filter function and include both an OR and an AND condition in the same formula. I knew it was possible but it took a few minutes to figure out the language. Great video.
The Best video that Explain Filter Function .. the other great thing is the practice problem that you put in filter problem .. watch and test your self 😍❤ Realy you are a great Chandoo ❤❤
Tend to stay away from these new functions because sharing the spreadsheet with others who may not have the updates becomes problematic. You can do the same with formulas anyway. I would use a cheat sheet with a nested IF formula then extract with something like =IFERROR(INDEX($C$5:$C$1004,SMALL(IF($C$5:$C$1004"",ROW($C$5:$C$1004)-ROW($C$5)+1),ROWS($N$5:N5))),"") then SHIFT ALT DEL to filter.
If you get the count = 1, you are referencing a spill cell, not the column filtered....also notice that COUNTA only works with a range where you are returning a single column, else it counts every cell.
Your videos are excellent learning opportunities. I am trying to solve the following question: I have a list of complaints received for each fiscal year and the findings for each. I am tasked with finding the top 10 complaints per fiscal year, and then OF THOSE TOP 10, how may of them have findings = substantiated. How do I use Filter to find the top 10 occurrences? And then of those, how do I determine how many were substantiated? Sounds simple, but I am a novice when it comes to dynamic arrays.
Hi Chandoo... I like your video tuitorial very much... Hat would be great if you could kindly always enlarge te fonts, formula and data range...it will surely help more audiences who wish to learned directly from you...TQVM..🙏
Thanks for the lovely feedback. I try to enlarge things when writing a formula or showing important things. You can also download the sample workbook (link in the description) or if you are watching on mobile enlarge the video with your fingers (just like you do with pictures). Happy learning.
thankyou Chandoo, so for example if people don't use common ways of entering data you could filter the tabe, begins with xxx and it would filter any words beginning with xxx and so on. ,... brilliant
Chandoo yeah I’d guess not - but I haven’t tried it, it’s not one you would use often. Filter is such an amazing function, filter wrapped in filter or index can do some pretty awesome stuff!!
Since False = 0 and True = 1, the results of your operations should end-up giving you either value. Thus, with „-„ if you meet the second condition and not the first, you shall seek an absolute amount from your test.
@@VideoAndrega Usually False is coded as 0 and true is coded as not false = -1. That beeing said: You should never ever use arithmetic functions on boll values. This is bad practice for a number of reasons, first and foremost this: No one garantees that integer representation of true and false is stable. The operations you want are AND(), OR() and NOT() in Excel. However: These functions can not be used for the filter function. This is where Microsoft really dropped the ball
Humor and knowledge. The best kind of learning
🤣💡 I call it laugh bulb moments.
I showed this to my Boss now he is my new intern 😎😎
thanks chandoo ☺️❤️
Took me longer than expected but finally got it right! Exercise 2, How many people have joined in the first 3 months of 2018 & 2019 (My solution):
=FILTER(staff[Date Joined],(MONTH(staff[Date Joined])
Chandooo you are EXCEL GURU I swear u are so good in formulars, best teachers in RUclips
Thank you Bin 😍
True. Chandoo has been my sensei since 2016 and I always have to check on his videos.
My daughters are starting to learn from him and I feel blessed.
Thanks, Mr Chandoo
@@nqatha2229 ❤
Але не кращий
Yes he is right Sir
The # is a life saver for dynamic selection. I have struggled with this in the past. Thank you for the great video.
The problems that you had provided as exercises were very tricky and it really helped me to understand the filters more deeply. Thanks a lot!
Hi, Have you used this formula for the last exercise =FILTER(sales,(sales[Sales Person]"Gunar Cockshoot")*(sales[Amount]>(AVERAGE(FILTER(sales[Amount],sales[Sales Person]="Gunar Cockshoot")))))
I watched and read many sources explaining FILTER and nothing else clicked. This video alone enabled me to create the spreadsheet I envisaged but didn't know how to achieve and I can't thank you enough!
Wow.. that is awesome to hear. More power to you RM ⚡
I think you are by far the best teacher I ve come accross for Excel. Thank you for all the knowledge. You are doing a great job. Keep it up.
Your videos make uninterested excel more fun and rememeber all options you mention... As always hats off to you Chandoo...
I purchased a course worth 600 from Udemy.
But that was Worth nothing..
And here's your free course is real worth
Thanks ❤❤❤
Thanks to your video, I also find out we can use SUMPRODUCT to sum all values in case of displaying filtered data and get the sum at the same time.
Geez, a complete unknown formula for me! Great comprehensive and funny video! Can’t wait to be next morning to try this on my spreadsheets! Thank you for the discovery 😍
I saw this video earlier...but practically there was no requirement. Now I got a situation to use Filter...I immediately referred this video...thank you chandoo garu...u r excel God
Thanks Chandoo,
This was exactly what i was looking for tried on a sample data and it worked.
=FILTER(A2:C12,(A2:A12="Anambra")*(B2>=50000)*(B2
That is awesome :)
The more I learn filter, the more I appreciate pivot table. Its all so easy for pivot table.
Thankyou Chandoo. I used to dread filtering the data and copy-pasting it onto a separate sheet. This is really helpful!
Hi Chandoo you are such a great mentor for the people who wants to learn more & more advance techniques of data analytics, I have attended so many lectures of excel, Query and Power Query but I always end up my learning with your lectures only.
I have gone through your video but i am feeling difficulty in exercise Sr. no. 2 & 5 (Completed). can you please share the formula for these exercise ?
Can combine with the UNIQUE function to e.g. give a list of unique staff in months only when they are paid. Thank you for the tutorial Chandoo!
Great idea!
This is an amazing tutorial; filled with detailed questions, in a simple way, with a sense of humor - love it!
Thank you for the great Filter video. I had fun practicing with it this morning.
That is awesome to hear 😀
Thank u chandoo for this amazing tutorial.I have made a report by using your array formulas unique and countif in ten minutes
Im so happy i came across this channel. Chandoo has me feeling unstoppable. You are the GOAT!
It was just yesterday I discovered filter function, however I had no idea about all these amazing ways this function can be used. Thanks for great video Chandoo 👍
Wow... that is a coincidence. 😀
@@chandoo_ a nb utesseco lit
Please tell me i watched videos for beginners to intermediate level on this channel and here i didn't understand in last tricky formula in playlist videos aren't in sequence which one I'd watch?
Thank you Chandoo (and of course Chan2)! All doubts about FILTER function are indeed filtered now! Keep it up! 😊👍
Thanks Vijay :)
One of the most useful videos of my life today. Fantastic.
Thank you Chandoo, A well presented and paced video covering some fairly advanced topics. 👍
Awesome teaching. Thank you for sharing your knowledge and experience.
I am so thrilled to see and practice all these features in Excel. And I do them for sheer joy. Though I did my engg in civil, I have been interested in DA. Though I have joined - in the race a bit late, I am so grateful to utubers like you who are sharing their knowledge so freely to the world. More success and power to you... keep up the good work... 👍👍
wonderful way to teach !
Thanks !!!!
As ALWAYS, Chandoo being the BEST! For Excel tips and Power BI tips, Chandoo is great. Thank you very much Chandoo for sharing knowledge
Sir you tells in simply method in filter function thank you
Ha! Only yesterday I had to use the Filter function and include both an OR and an AND condition in the same formula. I knew it was possible but it took a few minutes to figure out the language. Great video.
Does it havevto be excel365
@@silvana11221122 yes, as far as I know.
That is great. I am glad you enjoyed this :)
@@chandoo_ yes shared it on the work huddle this morning
Love the humour in the beginning, gave the video a like straight away..
Thanks Hitesh 😀
If I could, I would like the video twice. This helped me a lot.
Great video 👍
Aww.. so sweet. I can thank you twice for saying it though.
Thank you. Thank you :)
I called this filter function demystified,coming from the master himself.
Kudos and thanks for the good work as always.
Nice and informative. Greetings from Mordor!
Man, i just love the way you teach excel, you should get a prize for that. Thank you!!!
Wow, thanks!
Gunar had a tough childhood…
Was wondering who Gunar was till I spotted him
in filters i found what i was looking for, :D thanks
The Best video that Explain Filter Function .. the other great thing is the practice problem that you put in filter problem .. watch and test your self 😍❤
Realy you are a great Chandoo ❤❤
Chandoo Bhaiya, Maza aa gaya, Its Excel My Mind.......... I Love the MS Excel........
Your way of teaching is funny 😂 i like it
Thanks for the amazing video.
Fantastic!!!!! Chandooooooooooooooooooo!!!!!!!
Thank you, Chandoo. As always, top-notch content❤
I used to refer to ur website all the way back in 2014! what a discovery keep up the good work!
Welcome back!
Hello sir, I tried this, it's amazing. You are really awesome in excel.. and your humour is the best part of learning
Hi Chandoo, Your presentation is so clear. It shows your highly skill and experienced person. Thank so much for explanation🎉🎉🎉
You are welcome Ramesh :)
Awesome. Thank you for sharing
WOW..... thats really awsome function Chandoo.......Thanks a ton....
Thanks Shaik :)
Thank you very much for the data file and the exercises for me to practice. God Bless you always!
Tend to stay away from these new functions because sharing the spreadsheet with others who may not have the updates becomes problematic. You can do the same with formulas anyway. I would use a cheat sheet with a nested IF formula then extract with something like =IFERROR(INDEX($C$5:$C$1004,SMALL(IF($C$5:$C$1004"",ROW($C$5:$C$1004)-ROW($C$5)+1),ROWS($N$5:N5))),"") then SHIFT ALT DEL to filter.
Excellent video! thank you so much
Excellent video! Very helpful and love the flow of the tutorial. Makes it easy to follow and understand 👏🏼
This would eliminate the use of Advance Filter in Excel.
I'm about to make some training hitlists for work using =filter!
Thanks for the tutorial
Dear Chandoo, humorous. useful, perfect. Thank you.
Glad you liked it!
Such a great video.. Thanks a lot sir.. Keep us teaching..
Awesome explanation
A Perfect Teacher, with a Great Artistic Intelligence Application
Very informative ...thanks
I am extremely thankful for this video..not only this video..there are much more videos of you. But =counta(c5#) is always giving me result as 1
If you get the count = 1, you are referencing a spill cell, not the column filtered....also notice that COUNTA only works with a range where you are returning a single column, else it counts every cell.
Very very nice, congrats man!!! Thanks.
Thanks for watching!
Thanks chandoo sir for sharing the knowledge
It's my pleasure
"Not that one you idiot!" - Brilliant! ❤❤
This is actually nice and a boost to my inspirations
Thanks Chandoo. Great tips! FILTER is an awesome function :)) Thumbs up!!
Glad you think so!
why didn't I saw this earlier? I've been struggling with the AND and OR function the whole time !!!
awesome video thanks a lot for your insights 🙌
Great content. Very useful. And fun! Thanks from Peru
Thanks for sharing the wonderful tricks
Very useful, thanks. I see many similarities between the FILTER function arguments and the array formulas for counting and summing.
I have been laughing my socks off. And very informative as well. Thx Chandoo😂
Good job, Chandoo!
Very powerful. Thanks for the tips. Great video!
very well explained ...
New to learn. Thanks for sharing with good example
Can't Thank you enough for sharing so much knowledge sir.
Very good video! Congrats!
Thank you very much!
You are absolutely amazing Chandoo, just love your tips and tricks.
you always add value in my contents
Thanks
Your videos are excellent learning opportunities. I am trying to solve the following question: I have a list of complaints received for each fiscal year and the findings for each. I am tasked with finding the top 10 complaints per fiscal year, and then OF THOSE TOP 10, how may of them have findings = substantiated. How do I use Filter to find the top 10 occurrences? And then of those, how do I determine how many were substantiated? Sounds simple, but I am a novice when it comes to dynamic arrays.
Awesome. Thanks Chandoo.
My pleasure
How you are memories all the strategic?? What a Brain 🧠🧠💪💪
Good job and funny video!
Thank you, Sir, 🙏🌺😇
Thanks Chandoo Bhai. You make learning excel a Fun.
Chandu bhai you are clear, to the point tutor. 🙏👍👍
Hi Chandoo... I like your video tuitorial very much... Hat would be great if you could kindly always enlarge te fonts, formula and data range...it will surely help more audiences who wish to learned directly from you...TQVM..🙏
Thanks for the lovely feedback. I try to enlarge things when writing a formula or showing important things. You can also download the sample workbook (link in the description) or if you are watching on mobile enlarge the video with your fingers (just like you do with pictures). Happy learning.
I love those sound efects XD.
🦆🤣
thankyou Chandoo, so for example if people don't use common ways of entering data you could filter the tabe, begins with xxx and it would filter any words beginning with xxx and so on. ,... brilliant
Yes, FILTER makes so many things possible.
Excellent video
Glad you liked it
Amazing video sir.
Chandoo, thank so much for sharing and the way you make things easy to understand.
Thank you for the useful hints.
My pleasure!
Great video- well explained. Another operator worth exploring is the “-“ minus operator, where you only want 1 condition satisfied.
Great tip! That is brilliant. I don't think it will work beyond two conditions though.
Chandoo yeah I’d guess not - but I haven’t tried it, it’s not one you would use often. Filter is such an amazing function, filter wrapped in filter or index can do some pretty awesome stuff!!
Since False = 0 and True = 1, the results of your operations should end-up giving you either value. Thus, with „-„ if you meet the second condition and not the first, you shall seek an absolute amount from your test.
@@VideoAndrega Usually False is coded as 0 and true is coded as not false = -1.
That beeing said: You should never ever use arithmetic functions on boll values. This is bad practice for a number of reasons, first and foremost this: No one garantees that integer representation of true and false is stable.
The operations you want are AND(), OR() and NOT() in Excel. However: These functions can not be used for the filter function. This is where Microsoft really dropped the ball
@@r.igormortis149 Thank you for your explation. I will consider your valuable advice in my practice. Best Regards.
Very helpful
Chandoo's Excel is easy to digest. I salute your effort sir!
Thanks
It's new in excel but already available in google sheet
Thank you really informative, learned a few things, understood it all!