10 *next level* SUMIFS tricks only Excel Pros KNOW

10 next level SUMIFS tricks only Excel Pros KNOW

Рет қаралды 65,259

Күн бұрын

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

Пікірлер: 145

@utubeAgape 3 жыл бұрын

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!

@chandoo_ 3 жыл бұрын

You are welcome Irene...

@MyOprime 2 жыл бұрын

Your videos of teaching very fast. Pls teach clearly, so that we can understand

@raseshs1056 2 жыл бұрын

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!

@senpai.5517 2 жыл бұрын

Another video full of awesomeness, Chandoo! Thank you.

@supermario3322 2 жыл бұрын

Truly mind blowing. As a new excel learner, I got this but will take sometime to use it readily. Thanks for being awesome!!

@dhananjaypinjan2643 3 жыл бұрын

Fantastic and mind blowing.. Thanks for sharing 👍

@wayneedmondson1065 3 жыл бұрын

Awesome Chandoo! Great SUMIFs (and other functions) workout. Thanks for sharing :)) Thumbs up!!

@chandoo_ 3 жыл бұрын

Thanks Wayne... :)

@deepk82 3 жыл бұрын

Your excel tricks are always exciting! But this time I enjoyed video editing more than anything! Overall awesome video!!!👍👍👍👍👍

@Soulenergy31 3 жыл бұрын

Second the motion!!!!! I loved the energy shown on the Excel tips explained

@djones2055 3 жыл бұрын

I have been following you for years. Keep up the great work!!

@chandoo_ 3 жыл бұрын

Awesome! Always a pleasure to hear from long time followers like you.

@aiasaiascon3894 2 жыл бұрын

WOW!!!!! Fantastic!!!! You Rock!!!!

@ProjectAna Жыл бұрын

You are a lifetime savior 🎉🎉🎉🎉 THANK YOU very much for spreading knowledge❤

@raulmorales1656 2 жыл бұрын

Great examples, thank you for sharing!.

@chandoo_ 2 жыл бұрын

Glad you enjoyed it!

@caribeiro77 3 жыл бұрын

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...

@chandoo_ 3 жыл бұрын

Thank you. I am glad you enjoyed this. I had loads of fun shooting and editing the video.

@jaydingiesler5280 2 жыл бұрын

I LOVED THE HELMET. Your subtle humor is excellent. I really enjoy your channel… 😄

@chandoo_ 2 жыл бұрын

It' dangerous to go alone! Take this: ⛑

@shoaibrehman9988 3 жыл бұрын

Looks cool in new get-up. Thanks nice ideas.

@chandoo_ 3 жыл бұрын

You are welcome :)

@praffulsharma5615 2 жыл бұрын

Thank you Chandoo.

@excelworx8712 Жыл бұрын

really crazily insane!

@billtukana1111 3 жыл бұрын

Many thanks.

@florincopaci6821 3 жыл бұрын

Hello Thank you for another amazing video from which i learn a lot. All the best

@chandoo_ 3 жыл бұрын

Glad to hear that...

@bc4198 3 жыл бұрын

Great helmet joke! You and your channel are delightful and informative. Thank you!

@chandoo_ 3 жыл бұрын

I am glad it *_clicked_*

@Bhavik_Khatri Жыл бұрын

Excellent tutorial

@balakrishnanp7190 3 жыл бұрын

Wow, great Chandoo. Both tips and your expressions.

@chandoo_ 3 жыл бұрын

Glad you liked it!

@gregorytaylor2749 3 жыл бұрын

Thanks Chandoo for the excellent tutorial

@chandoo_ 3 жыл бұрын

You are most welcome Gregory.. :)

@ronakkumarsethia9272 3 жыл бұрын

It was really a great video, thank you 🤯

@chandoo_ 3 жыл бұрын

You are welcome Ronak...

@swadhinkumarmallia9168 Жыл бұрын

i just love this video

@vikramraghuwanshi9455 3 жыл бұрын

Excellent , OUtstanding., MIndblowing.about the sumifs formulae in microsoft Excel.

@chandoo_ 3 жыл бұрын

haha.. thank you Vikram.

@alexrosen8762 3 жыл бұрын

Great stuff! Thanks :-)

@chandoo_ 3 жыл бұрын

My pleasure!

@arunrai2909 3 жыл бұрын

Its just awesome!!!

@chandoo_ 3 жыл бұрын

😍😀

@CAsarathk 3 жыл бұрын

Pls make a video on xlookup advanced features like wild card ... etc... Thank u...

@chandoo_ 3 жыл бұрын

It is on the cards.. :)

@CAsarathk 3 жыл бұрын

@@chandoo_ 👍👍👍👍

@sufiyansiddiqui3061 3 жыл бұрын

It's really use full Thx a lot for your video

@chandoo_ 3 жыл бұрын

You are welcome Sufiyan...

@ramzaniyakoob4457 Жыл бұрын

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

@casairamk2790 3 жыл бұрын

Good Animations, it has given different feeling.. thanks chandoo..

@chandoo_ 3 жыл бұрын

Thank you Sai Ram...

@pgkannan 3 жыл бұрын

Thank you so much

@pvrajesh235378 3 жыл бұрын

Dear Chandoo, this video is really mind blowing. Helped me alot 🙂

@chandoo_ 3 жыл бұрын

Thank you Venkata...

@fantamadydoumbouya1526 2 жыл бұрын

I love this guys

@HusseinKorish 3 жыл бұрын

Yes ...it is mind blowing ... Thanks

@chandoo_ 3 жыл бұрын

Most welcome

@siddharthsatpathy2443 3 жыл бұрын

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

@chandoo_ 3 жыл бұрын

Thanks Sid. I have a video on the topic coming soon. Stay tuned.

@Dev_Bartwal Жыл бұрын

You are awesome 👍👍

@CintiaGNava 3 жыл бұрын

I was impressed with using wildcards with the function SUMIFS

@chandoo_ 3 жыл бұрын

Thank you...

@shreyanshdangi4366 Ай бұрын

love this video and learns a lot from this

@EminentJade 3 жыл бұрын

this is gold..... i mean all your vids are.

@chandoo_ 3 жыл бұрын

Thanks Eminent :)

@mohammedasifshaikh622 3 жыл бұрын

Excellent...

@chandoo_ 3 жыл бұрын

Thank you! Cheers!

@rameshwarprasaad3913 3 жыл бұрын

Amazing! Big fan f yours. Thanks!

@chandoo_ 3 жыл бұрын

You are welcome Rameshwar.

@SuperDarekR 3 жыл бұрын

Wowwww!!! Thanks

@chandoo_ 3 жыл бұрын

You are welcome Robert...

@supreethsharma4042 Жыл бұрын

woah you are a genius

@rahimullahshah5340 4 ай бұрын

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!

@AftabAhmed-uw4ki Жыл бұрын

Nice

@cursodeexcelcompletogratis7773 3 жыл бұрын

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.

@hussainfawzer 3 жыл бұрын

excellent explanation. you nailed it brother!

@chandoo_ 3 жыл бұрын

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.

@ysaleung Жыл бұрын

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.

@bharathramc.n7796 3 жыл бұрын

The 10th eg was awesome Please do some video on Sumproduct

@chandoo_ 3 жыл бұрын

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.

@vijayarjunwadkar 3 жыл бұрын

WOW! Very nice and useful tips Chandoo! You are a great teacher! Keep it up! Waiting for more such advanced videos! 😊👍

@chandoo_ 3 жыл бұрын

Thanks a lot 😊

@akshay-iq3qn2jp2c 3 жыл бұрын

#chandoo.....plz make vedio on basics of sumifs also

@Mr_Candid 3 жыл бұрын

Hai. Is there any formula for autopick up based on condition like which one is small

@ajayshirwadkar1625 Жыл бұрын

Please share solution for Excel get hangs/slows when filtering the SUMIFS on large data

@Prasadtr 3 жыл бұрын

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

@chandoo_ 3 жыл бұрын

Hey Prasad... Thanks. Here is my recommended mic & other video recording gear - www.amazon.com/shop/chandoo?listId=13T7P87OWEB7I&ref=idea_share_inf

@cherianiype 2 жыл бұрын

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!

@Mr_Candid 3 жыл бұрын

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

@canirmalchoudhary8173 3 жыл бұрын

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.

@eatcake7572 3 жыл бұрын

Chandoo is the funnest Excel Guru

@chandoo_ 3 жыл бұрын

Love this... I want to make videos that are AWESOME.

@darlenecakelifter 3 жыл бұрын

Can you make a cell appear like blinking to signify alarm (value of the cell is below standard) ?

@excelworx8712 3 жыл бұрын

Dont know about blinking. But conditionally formatting using 2 contrasting colours give an amazing effect

@Puneetjainaca 2 жыл бұрын

How to do sumif for a all the cells highlighted with a particular highlighted color or on basis of any Conditional formatting

@chandoo_ 2 жыл бұрын

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.

@DeeAdamsDillingham Жыл бұрын

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.

@oliverscheurich9202 Жыл бұрын

=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.

@9029710280 3 жыл бұрын

Can you please make video on more sumproduct formulas i need to calculate AHT with multiple data

@chandoo_ 3 жыл бұрын

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?

@9029710280 3 жыл бұрын

@@chandoo_ AHT Average Handling Time of call

@pandharinathjoshi6565 3 жыл бұрын

Nice very nice sir.., make office 365 new formulas with Advanced sir pls

@chandoo_ 3 жыл бұрын

You are welcome. Yes, more such videos coming soon.

@ARIFSAIFIFARHAN 2 жыл бұрын

I consider you both as my inspired person ( AR Rah-man)=(Chandoo)

@deegadams 3 ай бұрын

What about sumifs with xlookup

@florincopaci6821 3 жыл бұрын

Maybe in the future you will do a video with few examples using MMULT.Please.Thank you

@chandoo_ 3 жыл бұрын

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?

@florincopaci6821 3 жыл бұрын

@@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

@Kingx7565 3 жыл бұрын

Your Excel tricks are always good please explained a bit slowly you are too fast to speak it's a suggestion

@chandoo_ 3 жыл бұрын

Thank you Hasnain...

@asimsiddiq475 2 жыл бұрын

very surprising

@Sathesh-msk Жыл бұрын

Wow

@RMKserve 2 жыл бұрын

Please dedicate a video om comparison of Vlookul, index match, SUMPRODUCT Can all these formulas replace each other

@chandoo_ 2 жыл бұрын

Good idea CPO... I have a few vids on the topic already. Check out the playlist - kzfaq.info/get/bejne/btikqaeCu7fFcWQ.html

@maikilreategui1271 Жыл бұрын

Why isnt the 2D sumifs working with me?

@fruitycigar Жыл бұрын

Gunar Cockshoot is a really cool name. I'm going to use that as an alias to check into a shady hotel someday.

@bruh-cf9up Жыл бұрын

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

@chandoo_ Жыл бұрын

Hi Bruh.. this is an "Advanced" video. Please watch the build block videos on formulas first - kzfaq.info/get/bejne/eJuYlNx3ytKsdIk.html

@ronaldpinto3376 3 жыл бұрын

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.

@chandoo_ 3 жыл бұрын

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.

@ronaldpinto3376 3 жыл бұрын

@@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.

@nikhilsoni1177 3 жыл бұрын

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.

@dustuchele007 2 жыл бұрын

@Chandoo Ji, i feel the recommended playback speed for your videos should be 0.75x or even 0.50x, thoughts ?

@chandoo_ 2 жыл бұрын

This one was a bit fast as it is an Advanced video. I suggest watching this first. kzfaq.info/get/bejne/eJuYlNx3ytKsdIk.html

@dustuchele007 2 жыл бұрын

@@chandoo_ sure! All videos are great 😀

@captainmartine Жыл бұрын

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 )

@denismazzucato114 3 жыл бұрын

For "Female or Almond Choco" a (more understandable) alternative is: sumifs "Female" + sumifs "Almond Choco" - sumifs "Female and Almond Choco"

@chandoo_ 3 жыл бұрын

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

@hazemali382 3 жыл бұрын

👍🙏🌟💖

@kalmkrew 2 жыл бұрын

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?

@chandoo_ 2 жыл бұрын

Thanks for the feedback. I try different edit / recording styles. As this is an "ADVANCED" video, I kept the pace fast.

@harshitsingla 2 жыл бұрын

Stoa army hit the like

@subratkumarpurohit8686 6 ай бұрын

oops!! I forgot to wear a helmet!! will watch again wearing it

@anishabasu4709 Жыл бұрын

Kindly go little slow, otherwise you rock.

@chandoo_ Жыл бұрын

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 KZfaq or pause the video from time to time. Happy learning :)

@shriram8088 3 жыл бұрын

Please do not hurry while putting formula. Its take time to us to understand formula when you do it in seconds

@chandoo_ 3 жыл бұрын

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.

@shriram8088 3 жыл бұрын

@@chandoo_ Thanks Chandoo for your reply.

@taizoondean689 3 жыл бұрын

Sir, you are too fast. please go slow 🙏

@chandoo_ 3 жыл бұрын

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.

@taizoondean689 3 жыл бұрын

@@chandoo_ Thank you Sir. I will also improve on learning things fast. Thank you again for posting Videos on excel 👍