10 *next level* SUMIFS tricks only Excel Pros KNOW

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

[@utubeAgape]

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_]

You are welcome Irene...

[@MyOprime]

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

[@raseshs1056]

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]

Another video full of awesomeness, Chandoo! Thank you.

[@supermario3322]

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

[@deepk82]

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

[@Soulenergy31]

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

[@dhananjaypinjan2643]

Fantastic and mind blowing.. Thanks for sharing 👍

[@wayneedmondson1065]

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

[@chandoo_]

Thanks Wayne... :)

[@djones2055]

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

[@chandoo_]

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

[@ProjectAna]

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

[@jaydingiesler5280]

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

[@chandoo_]

It' dangerous to go alone! Take this: ⛑

[@raulmorales1656]

Great examples, thank you for sharing!.

[@chandoo_]

Glad you enjoyed it!

[@caribeiro77]

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_]

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

[@balakrishnanp7190]

Wow, great Chandoo. Both tips and your expressions.

[@chandoo_]

Glad you liked it!

[@florincopaci6821]

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

[@chandoo_]

Glad to hear that...

[@shoaibrehman9988]

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

[@chandoo_]

You are welcome :)

[@bc4198]

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

[@chandoo_]

I am glad it *_clicked_*

[@aiasaiascon3894]

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

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

[@gregorytaylor2749]

Thanks Chandoo for the excellent tutorial

[@chandoo_]

You are most welcome Gregory.. :)

[@pvrajesh235378]

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

[@chandoo_]

Thank you Venkata...

[@siddharthsatpathy2443]

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_]

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

[@ronakkumarsethia9272]

It was really a great video, thank you 🤯

[@chandoo_]

You are welcome Ronak...

[@casairamk2790]

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

[@chandoo_]

Thank you Sai Ram...

[@alexrosen8762]

Great stuff! Thanks :-)

[@chandoo_]

My pleasure!

[@sufiyansiddiqui3061]

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

[@chandoo_]

You are welcome Sufiyan...

[@vikramraghuwanshi9455]

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

[@chandoo_]

haha.. thank you Vikram.

[@CAsarathk]

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

[@chandoo_]

It is on the cards.. :)

[@CAsarathk]

@@chandoo_ 👍👍👍👍

[@excelworx8712]

really crazily insane!

[@praffulsharma5615]

Thank you Chandoo.

[@Bhavik_Khatri]

Excellent tutorial

[@pgkannan]

Thank you so much

[@rahimullahshah5340]

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!

[@billtukana1111]

Many thanks.

[@arunrai2909]

Its just awesome!!!

[@chandoo_]

😍😀

[@swadhinkumarmallia9168]

i just love this video

[@CintiaGNava]

I was impressed with using wildcards with the function SUMIFS

[@chandoo_]

Thank you...

[@HusseinKorish]

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

[@chandoo_]

Most welcome

[@EminentJade]

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

[@chandoo_]

Thanks Eminent :)

[@Prasadtr]

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_]

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

[@cursodeexcelcompletogratis7773]

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]

excellent explanation. you nailed it brother!

[@chandoo_]

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.

[@Dev_Bartwal]

You are awesome 👍👍

[@fantamadydoumbouya1526]

I love this guys

[@vijayarjunwadkar]

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

[@chandoo_]

Thanks a lot 😊

[@rameshwarprasaad3913]

Amazing! Big fan f yours. Thanks!

[@chandoo_]

You are welcome Rameshwar.

[@cherianiype]

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!

[@SuperDarekR]

Wowwww!!! Thanks

[@chandoo_]

You are welcome Robert...

[@bharathramc.n7796]

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

[@chandoo_]

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.

[@mohammedasifshaikh622]

Excellent...

[@chandoo_]

Thank you! Cheers!

[@Mr_Candid]

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

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

[@Mr_Candid]

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

[@9029710280]

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

[@chandoo_]

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]

@@chandoo_ AHT Average Handling Time of call

[@AftabAhmed-uw4ki]

Nice

[@akshay-iq3qn2jp2c]

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

[@supreethsharma4042]

woah you are a genius

[@darlenecakelifter]

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

[@excelworx8712]

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

[@777ter]

Legend!

[@chandoo_]

😍😀

[@ajayshirwadkar1625]

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

[@canirmalchoudhary8173]

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]

Chandoo is the funnest Excel Guru

[@chandoo_]

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

[@Puneetjainaca]

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

[@chandoo_]

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.

[@florincopaci6821]

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

[@chandoo_]

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]

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

[@pandharinathjoshi6565]

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

[@chandoo_]

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

[@dustuchele007]

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

[@chandoo_]

This one was a bit fast as it is an Advanced video. I suggest watching this first. kzbin.info/www/bejne/eGbLkqx8mtF7erc

[@dustuchele007]

@@chandoo_ sure! All videos are great 😀

[@ARIFSAIFIFARHAN]

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

[@ronaldpinto3376]

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_]

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]

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

[@kumar30003]

Wow

[@Kingx7565]

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

[@chandoo_]

Thank you Hasnain...

[@fruitycigar]

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

[@RMKserve]

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

[@chandoo_]

Good idea CPO... I have a few vids on the topic already. Check out the playlist - kzbin.info/www/bejne/bqPXp3eHi7aUd5I

[@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 - kzbin.info/www/bejne/eGbLkqx8mtF7erc

[@asimsiddiq475]

very surprising

[@nikhilsoni1177]

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.

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

[@deegadams]

What about sumifs with xlookup

[@maikilreategui1271]

Why isnt the 2D sumifs working with me?

[@denismazzucato114]

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

[@chandoo_]

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]

👍🙏🌟💖

[@kalmkrew]

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_]

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

[@harshitsingla]

Stoa army hit the like

[@subratkumarpurohit8686]

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

[@shriram8088]

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

[@chandoo_]

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]

@@chandoo_ Thanks Chandoo for your reply.

[@taizoondean689]

Sir, you are too fast. please go slow 🙏

[@chandoo_]

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]

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

[@arunkumarramachandran5647]

This is very fast … could not follow and understand ji

[@chandoo_]

This is an "advanced" video. Feel free to refer to the other videos for slower narration.