10 *next level* SUMIFS tricks only Excel Pros KNOW
Вставка
- Опубліковано 27 лип 2024
- Take your SUMIFS & other ifs skills to next level with these 10 powerful techniques.
Sample file:
=========
chandoo.org/wp/wp-content/upl...
Topics covered in this video (timestamps):
==================================
0:00 - Introduction
0:26 - 2D SUMIFS
1:04 - SUMIFS with multiple AND conditions
1:39 - SUMIFS with OR condition
2:46 - SUMPRODUCT alternative
3:32 - Multi-condition lookup with SUMIFS
4:10 - Greater than, less than conditions
4:31 - SUM between dates
5:29 - Pattern matching (wildcards) with formulas
6:00 - More pattern matching
6:41 - MEDIANIFS, MAXIFS, MINIFS
8:06 - UNIQUE Count with COUNTIFS and new formulas
Resources to learn more on SUMPRODUCT
===================================
Introduction and basic usage - chandoo.org/wp/excel-sumprodu...
Advanced examples - chandoo.org/wp/advanced-sumpr...
Watch these next
==============
Dynamic Array formulas - • Learn about the *NEW* ...
Longer video (live stream) - • Beginner to Pro ~ Exce...
#advancedExcel - Наука та технологія
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
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!
Another video full of awesomeness, Chandoo! Thank you.
Truly mind blowing. As a new excel learner, I got this but will take sometime to use it readily. Thanks for being awesome!!
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
Fantastic and mind blowing.. Thanks for sharing 👍
Awesome Chandoo! Great SUMIFs (and other functions) workout. Thanks for sharing :)) Thumbs up!!
Thanks Wayne... :)
I have been following you for years. Keep up the great work!!
Awesome! Always a pleasure to hear from long time followers like you.
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: ⛑
WOW!!!!! Fantastic!!!! You Rock!!!!
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 Chandoo.
Many thanks.
Wow, great Chandoo. Both tips and your expressions.
Glad you liked it!
Pls make a video on xlookup advanced features like wild card ... etc... Thank u...
It is on the cards.. :)
@@chandoo_ 👍👍👍👍
really crazily insane!
Great examples, thank you for sharing!.
Glad you enjoyed it!
Excellent tutorial
Hello Thank you for another amazing video from which i learn a lot. All the best
Glad to hear that...
Looks cool in new get-up. Thanks nice ideas.
You are welcome :)
i just love this video
Its just awesome!!!
😍😀
Thanks Chandoo for the excellent tutorial
You are most welcome Gregory.. :)
Great helmet joke! You and your channel are delightful and informative. Thank you!
I am glad it *_clicked_*
Dear Chandoo, this video is really mind blowing. Helped me alot 🙂
Thank you Venkata...
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...
Yes ...it is mind blowing ... Thanks
Most welcome
It was really a great video, thank you 🤯
You are welcome Ronak...
Thank you so much
I love this guys
Good Animations, it has given different feeling.. thanks chandoo..
Thank you Sai Ram...
Great stuff! Thanks :-)
My pleasure!
Excellent , OUtstanding., MIndblowing.about the sumifs formulae in microsoft Excel.
haha.. thank you Vikram.
You are awesome 👍👍
I was impressed with using wildcards with the function SUMIFS
Thank you...
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.
this is gold..... i mean all your vids are.
Thanks Eminent :)
Excellent...
Thank you! Cheers!
Wowwww!!! Thanks
You are welcome Robert...
woah you are a genius
Nice
love this video and learns a lot from this
Amazing!
Big fan f yours.
Thanks!
You are welcome Rameshwar.
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.
Chandoo is the funnest Excel Guru
Love this... I want to make videos that are AWESOME.
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!
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.
WOW! Very nice and useful tips Chandoo! You are a great teacher! Keep it up! Waiting for more such advanced videos! 😊👍
Thanks a lot 😊
#chandoo.....plz make vedio on basics of sumifs also
I consider you both as my inspired person ( AR Rah-man)=(Chandoo)
Your Excel tricks are always good please explained a bit slowly you are too fast to speak it's a suggestion
Thank you Hasnain...
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.
Gunar Cockshoot is a really cool name. I'm going to use that as an alias to check into a shady hotel someday.
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.
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
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!
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
Hai. Is there any formula for autopick up based on condition like which one is small
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.
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 )
Nice very nice sir.., make office 365 new formulas with Advanced sir pls
You are welcome. Yes, more such videos coming soon.
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
Wow
very surprising
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
Please share solution for Excel get hangs/slows when filtering the SUMIFS on large data
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
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.
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 - ua-cam.com/video/B5hayFelHDU/v-deo.html
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.
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 - ua-cam.com/video/8rtvDQVQaA0/v-deo.html
@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. ua-cam.com/video/B5hayFelHDU/v-deo.html
@@chandoo_ sure! All videos are great 😀
What about sumifs with xlookup
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.
Why isnt the 2D sumifs working with me?
👍🙏🌟💖
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.
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
Stoa army hit the like
oops!! I forgot to wear a helmet!! will watch again wearing it
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.
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 UA-cam or pause the video from time to time. Happy learning :)
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.