Learning and having fun at the same time....it's how i can describe this awesome lesson....that helmet, to protect us from those bombastic tips, is amazing......... You really took a step further on how you teach...Thank you Chandoo...
Thank you again for another wonderful tutorial. Best part is that each case is supported by examples and your explanations cover older versions of excel as well. And Love the animations!
great video ! didnt get the #4 . Isnt missing the first line ? about the unique technique, the easiest way to understand why this trick works is understanding what happens when there is only 1 unique value. For example, you have a cake and you want to slice in 4, each slice is 0.25 and if you sum them, you will get one cake again. the countif is counting the number of piece of cakes each seller will share, so when we say 1 / number of piece of cakes, at the end each seller will share one 1 cake, it doesnt matter how many pieces.
Thank you. We are using SUMIFS to do the multi-condition lookup in #4. It works only if you have one matching value and the result you want is a number column. Awesome explanation on the unique count part. Donut for you.
Good video. Full of knowledge with straight result oriented approach for advanced level excel users. Pls make it with without PIVOT Table (with simple sheet for basic users). Thanks
Hello Chandoo, Thank you for yet another awesome video! Any plans to do a video on basic statistics in Excel? I think some of us may find it really useful Regards
Great. That was exceptional. Can you please guide me, How to use a single sumifs function to get the multiple sum ranges against multiple criterias. Is it possible? Thank You!
It would be really helpful if u slowed down amd explained the formula building step. I have to pause, rewind and at times open excel to get an understanding of what just happened
Bro.. i am very much willing to take your excel dashboard course but it the covid condition in india which made things worse you are a great teacher but for me the amount is very huge. Don't take it in wrong way.
You should also male video with understand make logic of formula how to make how to put/apply on interview and job and how understand accordingly to data to make formula in Advanced level Vlookeup or And , Time , Sumif IF vlookup choose , and ,or Sumifs , if ,or ,and ,filter , aggregate Using formula with making Mis Report on Pivot table. Like this advanced multi conditional formula how to develop and how to understand please make video on it next week earlier we want it. I genuinely request to you sir. Please reply me Chandoo Sir🙏 From India. Regards, Amar Pawar ( A HR Student )
Great , it covered all combination, I have one question , not related to EXCEL I can hear voice very clear, I would like to know which MIC you suggest. share the model , i would like to buy one
Good point. Keep in mind that it won't work, 1) for averages 2) when you want more than one OR condition - for ex: Female or Almond Choco or Customers>500
Thanks Chandoo for this... I have a question pls related to AVERAGEIF... I do not think this will work in 2D array... example - Average of a sales person across Jan, Feb & Mar. Can you pls advise if AVERAGEIF function works pls? Shukriya!
Hi! great video. Very informative. I was just confused with the SUMIFS and FILTER of female and almond choco being two different answers. When using SUMIFS its 107,900 but when summing up the FILTER then its equal to 872,230. If someone can clarify this for me please.
Good suggestion. MMULT is a powerful function but _kinda_ tricky to explain and apply for everyday business situations. Do you have any problems where it is the only option to use?
@@chandoo_ Hello Sir and Thank you for your prompt answer! I have seen people just a few who know very well Excel using this function of course in combination with other function such as Transpose,Match etc and is not so easy to understand this function. I can send you a file with few examples and maybe in the future when you will have some time you will take a peek.If it s possible of course. Once again many thanks for all the videos and the information you share with us I can wait the webinar with Power Pivot
Would countif work if I want to count several different text values? I have a spreadsheet with a column of Shifts with 2 letter codes. I need to count several different codes per day lumped together. like all BH, DN & M-F then the next day count FH,BH,M-F.
=SUM(COUNTIF(B4:B20,D2:F2)) In this example BH , DN and M-F are entered in cells D2, E2 and F2. The range containing the codes is B4:B20. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. The next day you can enter FH,BH,M-F in cells D2, E2 and F2.
Can you please suggest a formula to extract information about name of employees and date of leaving from a excel workbook. The details are available on different sheet, and the information is in common sheet for active and left employees.
Hi Ronald... You can either use FILTER() function or advanced filter to do this. See these page for details - Filter function - chandoo.org/wp/dynamic-array-functions/ Advanced Filter - chandoo.org/wp/how-to-use-advanced-filters/ All the best.
@@chandoo_ Thanks for looking at my post. I will go through the information provided in the link and try to understand it's working. Thanks once again.
Great information but A little too fast bro lol. Good thing I can slow down but I don't want to ruin the presentation of the video or to be constantly replaying a point, you know?
Hi Anisha... this is an "Advanced" video as the title suggests. But if you wish to slow down, you can use the "video speed" option of RUclips or pause the video from time to time. Happy learning :)
Hi Shriram.. Thanks for the suggestion. You can pause the video at formula screens and take notes. I also provide an example file on most videos. Download and you can see the actual formula in it. I like to keep the video fast paced so that no ones time is wasted.
Hi Taizoon.. sorry about that. You can always pause the video at important points or use the example file for this video to refer to the formulas. I will slow down or emphasise important bits in future videos. But in general, I like to keep them fast and tight so that people's time is not wasted.
LOVE the business use case examples - wish all the excel videos out there gave as many examples as you did in this great tutorial. Thank you!
You are welcome Irene...
Your videos of teaching very fast. Pls teach clearly, so that we can understand
చాలాకాలం క్రితం ఫ్లాష్ ట్యుటోరియల్ చూశాను. same ఇదే వాయిస్. మీరేనేమో అనుకొంటున్నాను. మీరు చెప్పే తీరు మాత్రం అద్భుతం!
Your excel tricks are always exciting! But this time I enjoyed video editing more than anything!
Overall awesome video!!!👍👍👍👍👍
Second the motion!!!!! I loved the energy shown on the Excel tips explained
You are a lifetime savior 🎉🎉🎉🎉 THANK YOU very much for spreading knowledge❤
I LOVED THE HELMET. Your subtle humor is excellent. I really enjoy your channel… 😄
It' dangerous to go alone! Take this: ⛑
Learning and having fun at the same time....it's how i can describe this awesome lesson....that helmet, to protect us from those bombastic tips, is amazing......... You really took a step further on how you teach...Thank you Chandoo...
Thank you. I am glad you enjoyed this. I had loads of fun shooting and editing the video.
Thank you again for another wonderful tutorial. Best part is that each case is supported by examples and your explanations cover older versions of excel as well.
And Love the animations!
Truly mind blowing. As a new excel learner, I got this but will take sometime to use it readily. Thanks for being awesome!!
Pls make a video on xlookup advanced features like wild card ... etc... Thank u...
It is on the cards.. :)
@@chandoo_ 👍👍👍👍
I was impressed with using wildcards with the function SUMIFS
Thank you...
Many thanks.
really crazily insane!
I have been following you for years. Keep up the great work!!
Awesome! Always a pleasure to hear from long time followers like you.
Another video full of awesomeness, Chandoo! Thank you.
i just love this video
Fantastic and mind blowing.. Thanks for sharing 👍
Chandoo is the funnest Excel Guru
Love this... I want to make videos that are AWESOME.
this is gold..... i mean all your vids are.
Thanks Eminent :)
Excellent tutorial
Great helmet joke! You and your channel are delightful and informative. Thank you!
I am glad it *_clicked_*
Excellent , OUtstanding., MIndblowing.about the sumifs formulae in microsoft Excel.
haha.. thank you Vikram.
great video ! didnt get the #4 . Isnt missing the first line ?
about the unique technique, the easiest way to understand why this trick works is understanding what happens when there is only 1 unique value. For example, you have a cake and you want to slice in 4, each slice is 0.25 and if you sum them, you will get one cake again. the countif is counting the number of piece of cakes each seller will share, so when we say 1 / number of piece of cakes, at the end each seller will share one 1 cake, it doesnt matter how many pieces.
excellent explanation. you nailed it brother!
Thank you. We are using SUMIFS to do the multi-condition lookup in #4. It works only if you have one matching value and the result you want is a number column.
Awesome explanation on the unique count part. Donut for you.
woah you are a genius
WOW!!!!! Fantastic!!!! You Rock!!!!
Yes ...it is mind blowing ... Thanks
Most welcome
Wow, great Chandoo. Both tips and your expressions.
Glad you liked it!
Your Excel tricks are always good please explained a bit slowly you are too fast to speak it's a suggestion
Thank you Hasnain...
Good video. Full of knowledge with straight result oriented approach for advanced level excel users. Pls make it with without PIVOT Table (with simple sheet for basic users). Thanks
It's really use full Thx a lot for your video
You are welcome Sufiyan...
Hello Thank you for another amazing video from which i learn a lot. All the best
Glad to hear that...
Dear Chandoo, this video is really mind blowing. Helped me alot 🙂
Thank you Venkata...
Awesome Chandoo! Great SUMIFs (and other functions) workout. Thanks for sharing :)) Thumbs up!!
Thanks Wayne... :)
Its just awesome!!!
😍😀
I love this guys
love this video and learns a lot from this
Thank you Chandoo.
Thanks Chandoo for the excellent tutorial
You are most welcome Gregory.. :)
You are awesome 👍👍
Great stuff! Thanks :-)
My pleasure!
I consider you both as my inspired person ( AR Rah-man)=(Chandoo)
Hello Chandoo, Thank you for yet another awesome video! Any plans to do a video on basic statistics in Excel? I think some of us may find it really useful
Regards
Thanks Sid. I have a video on the topic coming soon. Stay tuned.
Great examples, thank you for sharing!.
Glad you enjoyed it!
Great. That was exceptional. Can you please guide me, How to use a single sumifs function to get the multiple sum ranges against multiple criterias. Is it possible? Thank You!
Good Animations, it has given different feeling.. thanks chandoo..
Thank you Sai Ram...
I got the logic of Unique count. 1/COUNTIFS will divide the counting equally how many times it's repeated and SUM will add those up to make it ONE.
Looks cool in new get-up. Thanks nice ideas.
You are welcome :)
Gunar Cockshoot is a really cool name. I'm going to use that as an alias to check into a shady hotel someday.
Excellent...
Thank you! Cheers!
WOW! Very nice and useful tips Chandoo! You are a great teacher! Keep it up! Waiting for more such advanced videos! 😊👍
Thanks a lot 😊
It was really a great video, thank you 🤯
You are welcome Ronak...
#chandoo.....plz make vedio on basics of sumifs also
Thank you so much
The 10th eg was awesome Please do some video on Sumproduct
Sure. I will run a poll on Sumproduct to see if more people are interested in it. I kind of stopped using it after Dynamic Array functions rolled out.
Can you make a cell appear like blinking to signify alarm (value of the cell is below standard) ?
Dont know about blinking. But conditionally formatting using 2 contrasting colours give an amazing effect
It would be really helpful if u slowed down amd explained the formula building step. I have to pause, rewind and at times open excel to get an understanding of what just happened
Hi Bruh.. this is an "Advanced" video. Please watch the build block videos on formulas first - ruclips.net/video/B5hayFelHDU/видео.html
Bro.. i am very much willing to take your excel dashboard course but it the covid condition in india which made things worse you are a great teacher but for me the amount is very huge. Don't take it in wrong way.
Nice
You should also male video with understand make logic of formula how to make how to put/apply on interview and job and how understand accordingly to data to make formula in Advanced level
Vlookeup or And , Time , Sumif
IF vlookup choose , and ,or
Sumifs , if ,or ,and ,filter , aggregate
Using formula with making Mis Report on Pivot table.
Like this advanced multi conditional formula how to develop and how to understand please make video on it next week earlier we want it. I genuinely request to you sir.
Please reply me
Chandoo Sir🙏 From India.
Regards,
Amar Pawar ( A HR Student )
Amazing!
Big fan f yours.
Thanks!
You are welcome Rameshwar.
Wowwww!!! Thanks
You are welcome Robert...
Great , it covered all combination,
I have one question , not related to EXCEL
I can hear voice very clear, I would like to know which MIC you suggest.
share the model , i would like to buy one
Hey Prasad... Thanks.
Here is my recommended mic & other video recording gear - www.amazon.com/shop/chandoo?listId=13T7P87OWEB7I&ref=idea_share_inf
Please share solution for Excel get hangs/slows when filtering the SUMIFS on large data
For "Female or Almond Choco" a (more understandable) alternative is: sumifs "Female" + sumifs "Almond Choco" - sumifs "Female and Almond Choco"
Good point. Keep in mind that it won't work,
1) for averages
2) when you want more than one OR condition - for ex: Female or Almond Choco or Customers>500
very surprising
Please dedicate a video om comparison of Vlookul, index match, SUMPRODUCT
Can all these formulas replace each other
Good idea CPO... I have a few vids on the topic already. Check out the playlist - ruclips.net/video/8rtvDQVQaA0/видео.html
Thanks Chandoo for this... I have a question pls related to AVERAGEIF... I do not think this will work in 2D array... example - Average of a sales person across Jan, Feb & Mar. Can you pls advise if AVERAGEIF function works pls? Shukriya!
oops!! I forgot to wear a helmet!! will watch again wearing it
Can you please make video on more sumproduct formulas i need to calculate AHT with multiple data
Sure... I will think about this. With new Excel features, we can calculate outputs without using SUMPRODUCT all the time. What is the logic for AHT?
@@chandoo_ AHT Average Handling Time of call
Hi! great video. Very informative. I was just confused with the SUMIFS and FILTER of female and almond choco being two different answers. When using SUMIFS its 107,900 but when summing up the FILTER then its equal to 872,230. If someone can clarify this for me please.
Maybe in the future you will do a video with few examples using MMULT.Please.Thank you
Good suggestion. MMULT is a powerful function but _kinda_ tricky to explain and apply for everyday business situations. Do you have any problems where it is the only option to use?
@@chandoo_ Hello Sir and Thank you for your prompt answer!
I have seen people just a few who know very well Excel using this function of course in combination with other function such as Transpose,Match etc and is not so easy to understand this function.
I can send you a file with few examples and maybe in the future when you will have some time you will take a peek.If it s possible of course.
Once again many thanks for all the videos and the information you share with us
I can wait the webinar with Power Pivot
@Chandoo Ji, i feel the recommended playback speed for your videos should be 0.75x or even 0.50x, thoughts ?
This one was a bit fast as it is an Advanced video. I suggest watching this first. ruclips.net/video/B5hayFelHDU/видео.html
@@chandoo_ sure! All videos are great 😀
Wow
Stoa army hit the like
What about sumifs with xlookup
Nice very nice sir.., make office 365 new formulas with Advanced sir pls
You are welcome. Yes, more such videos coming soon.
I mean I have three products A B and C with some expiry date. Now I want to check and pick up the fastest expiring date . Is this possible
How to do sumif for a all the cells highlighted with a particular highlighted color or on basis of any Conditional formatting
We can't add up or do anything for highlighted cells thru formulas. If there is a CF rule, try to create the same logic in the formula.
Hai. Is there any formula for autopick up based on condition like which one is small
Why isnt the 2D sumifs working with me?
Would countif work if I want to count several different text values? I have a spreadsheet with a column of Shifts with 2 letter codes. I need to count several different codes per day lumped together.
like all BH, DN & M-F then the next day count FH,BH,M-F.
=SUM(COUNTIF(B4:B20,D2:F2))
In this example BH , DN and M-F are entered in cells D2, E2 and F2.
The range containing the codes is B4:B20.
Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
The next day you can enter FH,BH,M-F in cells D2, E2 and F2.
Can you please suggest a formula to extract information about name of employees and date of leaving from a excel workbook. The details are available on different sheet, and the information is in common sheet for active and left employees.
Hi Ronald... You can either use FILTER() function or advanced filter to do this.
See these page for details -
Filter function - chandoo.org/wp/dynamic-array-functions/
Advanced Filter - chandoo.org/wp/how-to-use-advanced-filters/
All the best.
@@chandoo_ Thanks for looking at my post. I will go through the information provided in the link and try to understand it's working. Thanks once again.
Great information but A little too fast bro lol. Good thing I can slow down but I don't want to ruin the presentation of the video or to be constantly replaying a point, you know?
Thanks for the feedback. I try different edit / recording styles. As this is an "ADVANCED" video, I kept the pace fast.
Kindly go little slow, otherwise you rock.
Hi Anisha... this is an "Advanced" video as the title suggests. But if you wish to slow down, you can use the "video speed" option of RUclips or pause the video from time to time. Happy learning :)
Please do not hurry while putting formula.
Its take time to us to understand formula when you do it in seconds
Hi Shriram.. Thanks for the suggestion. You can pause the video at formula screens and take notes. I also provide an example file on most videos. Download and you can see the actual formula in it. I like to keep the video fast paced so that no ones time is wasted.
@@chandoo_ Thanks Chandoo for your reply.
👍🙏🌟💖
Sir, you are too fast. please go slow 🙏
Hi Taizoon.. sorry about that. You can always pause the video at important points or use the example file for this video to refer to the formulas. I will slow down or emphasise important bits in future videos. But in general, I like to keep them fast and tight so that people's time is not wasted.
@@chandoo_ Thank you Sir. I will also improve on learning things fast. Thank you again for posting Videos on excel 👍
This is very fast … could not follow and understand ji
This is an "advanced" video. Feel free to refer to the other videos for slower narration.