How to Use SUMIFS, COUNTIFS and AVERAGEIFS in Excel (Multiple Criteria)
HTML-код
- Опубликовано: 16 июл 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Excel's SUMIFS function is a game-changer for accountants and analysts. Unlike the simpler SUMIF, which only allows for one condition, SUMIFS can handle up to 29 criteria. Introduced in Excel 2007, it has vastly simplified complex calculations without needing array formulas.
⬇️ Download the workbook here: pages.xelplus.com/sumifs-file
Here's why SUMIFS is the superior choice:
▪️ Multiple Criteria Handling: SUMIFS shines in real-life scenarios where sums are required with multiple exceptions. This function effortlessly manages several conditions, making it indispensable for detailed data analysis.
▪️ User-Friendly Syntax: Although the syntax of SUMIFS differs from SUMIF, placing the sum range at the beginning, it's intuitive and user-friendly. It makes it easier to include additional criteria without altering the core of the formula.
▪️ Dynamic Calculation: SUMIFS allows for more dynamic and flexible calculations. Whether you're summing sales for a specific product, month, and country, or applying various other filters, SUMIFS handles it seamlessly.
▪️ Wildcards and Comparison Operators: SUMIFS is versatile, supporting wildcards for partial matches and comparison operators for greater-than or less-than conditions. This adds another layer of flexibility, enabling more nuanced data analysis.
▪️ Integration with Excel Tables: When used with Excel Tables, SUMIFS becomes even more powerful. It offers dynamic referencing, meaning your formulas automatically adjust when new data is added, saving time and reducing errors.
▪️ Related Functions: The principles of SUMIFS extend to other useful functions like AVERAGEIFS and COUNTIFS, providing a consistent approach across various types of calculations.
The inclusion of the "IFS" formulas since Excel 2007 was a major gain for Excel users. Why? Because they allow you to easily add exceptions to your sum, average and count calculations. Don’t make your life more difficult by trying to use excel filters to get your sum. Use the SUMIFS formula instead. You can SUM based on multiple criteria. You can also use SUMIFS to sum if numbers are greater than a certain value.
00:00 Why use Excel SUMIFS instead of SUMIF
01:20 Excel SUMIF function
03:10 Excel SUMIFS function
06:06 SUMIFS with wildcards
06:47 SUMIFS with comparison operators
09:17 Excel AVERAGIFS function
09:43 Excel COUNTIF function
10:19 Excel COUNTIFS function
11:50 How to use Excel Tables for dynamic referencing
13:31 Wrap Up
In this video I will show you a SUMIFS example and explanation. AVERAGEIFS works in the same way.
COUNTIFS function is shorter than SUMIFS and AVERAGEIFS because it doesn’t need the additional sum range. It only needs the criteria range. It counts the number of times the criteria is met.
Not sure if you should use SUMIF or SUMIFS? Stick to SUMIFS. This way you're on the safe side. In case you ever need to add more criteria, you can just expand on the arguments inside the formula. If you used SUMIF, you actually have to replace it with SUMIFS.
➡️ Join this channel to get access to perks: / @leilagharani
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here: pages.xelplus.com/sumifs-file
@leilagharani Kindly help me with this problem. My boss is eating on creating an analysis table. The call time is eg. 8:24:55 AM and 5:44:67 PM, I need only the hour from here. But hour function is used but for pm it is showing in 24 hours format. But they want in number format only. How can I do it. The formula mention in the shet was numbervalue.. I'm not getting it with that formula please help..
Leila you have saved me so many tears these past months. I´ve got a task from my boss to make a 5 year-budget for the whole department in EXCEL and your tutorials have just helped so much!
I'm happy to hear that Vanesa!
@@LeilaGharani ok
Thanks
Excellent tutor, you explain fumula so clearly thank you Leila!
I've been struggling with SUMIF(s) all morning, this sorted my issue out immediately, thank you for sharing your knowledge!
I'm glad it was helpful!
Hi Leena ,
Please can you tell my how can i count a particular text in a column excluding certain rows , eg rows s2:s500 and count rows that say on track excluding text in row s4 / s13/s18
Mee too
@@mildredsouza5907 how about if while you use the formula, you press CTRL and count it individually 1 by 1.. I mean some kind like this =countif((s2:s12)+countif(s14:s17)+countif(s19:s500))
@@mildredsouza5907 i found farmula better than countifs watch and decide may its help you
ruclips.net/video/Uw5MiEEA_3A/видео.html
Leila, thank you so much! My team and I are building a histogram for a cruise line company’s spare parts list and we needed to find out how many vessels would receive the parts they ordered within a range of timeframes. The COUNTIFS function helped me do that easily and efficiently!
Leila, you are truly an inspiration to us all. Your excellent Excel videos saved my career. I cannot adequately thank you enough for everything. I am beyond forever grateful to you for the lecture videos. Looking forward to your future videos that are ALWAYS easy to understand.
I'm really glad you like the videos!
Excellent tutor, made things very simple and easy, thank you!
Very happy to hear that Jerry. You are very welcome.
You are soo good Liela .. absolute clarity on what you teach . Every video has at least 3-4 takeaways which help me speed up excel working ... been using SUMIF for ages .... willl switch to SUMIFS immediately !
OMG, you are so good at explaining. I prepare a monthly report for my client using pivots. I stopped using pivots. Now I use SUMIF. Thank you so much.
Great video! SUMIFS and the like are so powerful!!! And fun too : )
So True! Thank you Mike.
you saved me
ill watch all the rest
your way is simple to understand
Thank you Leila for supporting us with your great knowledge
Thanks Leila Gharani……. I saw your several videos which connects with excel functions and It has been very helpful to me.
Thank you once again...
This is a great refresher! Thank you, Leila!
Hi Leila, really I am a fan of your videos and techniques, and keep watching...thank you
Leila, you are a genius. I have used spreadsheets for years but in a really basic way - sum columns, subtotals etc. I started using MacNumbers and hit precisely the problem you showed: how do I populate cells with the results of several criteria. I could on the Sumif and that saved time. But I was trying functions like "AND" and could not get it to work. Then I wondered what the difference was between Sumif and Sumifs. I couldn't find anything (for MacNumbers) on it. Then I watched your video and kapow! The Mac worked in exactly the same way. Gone are the time consuming days of adding columns and transferring the results to a cell. Now, thanks to you, I can create a table how I want it and use Sumifs to populate the cells. Here's a virtual kiss on the hand and a big THANK YOU. Liked and subscribed.
@Leila Gharani You have covered subtle details and possible mistakes in very simplified way, it saved my day.
Great job and Thanks
Thanks Mam for sharing ur knowledge of Excel with us so we can too Excel in Excel.Thaks a lot.... and keep sharing....
You're very welcome Shiva. It's my pleasure. Thank you for your nice comment & your support.
Thank you Leila
Very smart & useful Excel techniques.
You're very welcome Hattem :)
Hattem
First of all thank you for your precious lectures on this topic, ma'am, its very helpful. Kindly upload all the series. Thanks
You have a gift! You make the seemingly complicated, remarkably simple. Now demystified Sumifs, countifs, Averageifs for all of us. Thanks Leila!
You're very welcome. Many thanks for the kind feedback!
THANK YOU
You're very welcome.
i don't trust people who can work with excel
😅lol, that can lead to a world debate.
Oh my gosh I've started a new job and I need to learn it, alongside everything else to learn...I'm so intimidated and they can sense I suck already 😅
Then consider yourself an idiot person. 😂
@cleamahon4277 I'm preparing for a final exam & I've just now started to get the hang of this....I think? 😅
Your videos are so amazing! Every time you teach me a new function and show how fast excel can get the results - I have to pause the video because I'm just stunned! Wow! I feel like I'm watching a wizard reveal her deepest secrets
Leila, you explained this so wonderfully. Like, the small details mattered such as when to click the criteria and where to FIND THAT criteria. It just made it so easy to understand. Please keep it up! Thank you!!
I'm very glad to hear that Adil! Thanks for your feedback and your support.
Just love the way she explains every formula. thanks a lot.
I'm so glad I've found your channel. you are very clear and concise, and you don't waste any time getting to the point. You've helped me so many times, now. Thank you
Many thanks for the kind feedback Jennifer. I'm glad to have you here.
Though Leila's videos are here on RUclips for years now but I noticed those only today. I don't know how many characters this RUclips comment area allows so I can endorse her work with the words those she deserves. I'm in the software field for the last 30 years but haven't seen such a tutor and neither I bothered to go out and start appreciating someone in this comment area. She is The Teacher, The Mentor, and let me say The Lady :) of course. Just Superb. Well done Leila!
Wow, thank you very much for the feedback. Makes me very happy :)
Leila, you are amazing! Thanks a lot for sharing your knowledge in a very clear way!
I’ve used the SUMIFS function before but just got stuck this morning. This refresher unblocked my brain! Thank you!!
Thank you Leila for explaining with such simplicity and brevity. Your videos are truly helpful and to the point.
I'm glad you like it! Thank you.
Come here looking for sumifs, turned out getting a lot more of useful information about excel. Thank a lot Leila, Good Luck
Glad it was helpful!
Many videos but this gave me complete all details...................Your teaching is giving me idea of exploring functions in different ways Thanks
Your tips are awesome, I keep visiting and revisiting when i have complex issues... Thank you
I've watched most of your videos if not all...
I owe a lot my knowledge in making financial performance reports of my clients not just faster to accomplished but most importantly in quality outputs.
You are one of my Excel mentors and I will forever be grateful of that.
Again thank YOU so much...to you, other brilliant excel tutors and to RUclips for this new platform of learning that is truly beneficial to people around the world. You've given us an access to the technology and innovations not just applicable to finance, accounting and business people but so many other important learning tools making complicated things and sitiations not just solved but solved faster.
More power Ms. Leila.
Wow, I really appreciate the kind feedback. It's so great to hear that you could take this knowledge to improve your work. Thanks for the support and keep learning :)
@@LeilaGharani ❤❤❤Thank You!!!❤❤❤
Thank you so much for your incredible lessons and precious time Leila! I got from no knowledge in excel to be able of working on my own project! only after 2 month of taking your courses.
I'm very happy to hear that! Many thanks for your support.
I think I will never forget what I learnt from this video. It is so helpful for the beginners. Thanks alot for explaing so well.
thank you so much Leila, you helped me figure out how to sumifs using multiple criteria, and not having to use range/table complexity. THANK YOU so much!
thank you so much for your lectures, I'm learning so much!
I have to say though, you are helping a lot of people! This improved me so much more and I can follow you so easily. I appreciate the effort that you put in to this for us
Thank you for the kind feedback Jeffrey. It makes me happy when the tutorials are helpful.
Excellent Tutor. It is easy to understand here, very well explained.
finally someone explains it simply! Thank you so much for your time and effort, Miss Gharani!
You're very welcome! Glad it is useful.
Amazing lecture Leila your speed of talking is exactly what someone need to absorb new information form a lecturer
You amaze me the more I watch your videos
I'm glad you find it easy to follow Kamel : )
Very useful for developing habit of reducing manual work and automate task. Thanks Leila. Expecting more from you.
You are welcome 😊
Thanks for this quick learning on sumif, sumifs.. I have learnt all in one go without any learning complexity. Thanks
Wow! I went from using:
=SUMPRODUCT((LEN(B2:31)-LEN (SUBSTITUTE ((B2:B31),K1,"")))/LEN (K1))
to using:
=COUNTIFS(B2:B31,"*"&K1&"*")
I was also able to start using * for many other equations! Thanks so much for this informative video!
Great job!
The part of this that helped me was using quotations around the comparison operators. Thanks!
Love your videos - you have no idea how much your videos help me with my work!!
SHE IS GREAT AND INSPIRED ME TO DO SOME TOO. check out this too
ruclips.net/video/1abjBrSqTnk/видео.html
ruclips.net/video/l80mLoaO_-o/видео.html
ruclips.net/video/UMNOkrAcE8A/видео.html
ruclips.net/video/SQbRatup2qU/видео.html
dont forget to like n subscribe.
Thank you Leila. You make some of the best Excel videos out there. And I could listen to your voice all day ;)
I'm glad to hear that :)
Thanks a ton! I was looking for Countifs formula with 3 criteria since last 2 days and finally I got it here :)
Glad it helped!
Thank you, Leila. Super clearly explained as usual!👋
OMG TQSM!! IVE BEEN SEARCHING MANY VIDEOS TO UNDERSTAND THIS FX FOR WEEKS IN ORDER TO SUBMIT MY PROJECT TO MY LECTURER 😭
Leila - You are a great teacher. Thank you so much
Thanks so much Leila! It is very useful and quickly understand due to your clear way of explaining. Looking forward others sharing from you.
Thank you Leila, very useful. Previously I used to implement Index-Match function for this calculation.
Found Farmula better than countifs
ruclips.net/video/Uw5MiEEA_3A/видео.html
Thank you Leia! I couldn't do these in my job excel assessment test. I am now prepared for next time. Your video is the best explaining these functions!
Thank you so much Leila, very clearly explained, and it's very useful to have the sample data worksheet to experiment and work alongside the video :)
GOT AN "A" IN MY MIST CLASS BASED PRIMARILY ON EXCEL....I HAVE NO ONE BUT YOU TO THANK. THANKS A MILLION LEILA YOUR VIDEOS ARE A BLESSING FOR STUDENTS LIKE US
That's great Natasha. Congratulations!
Thank you so much! One of the best channel on RUclips.
Thanks Leila, you make this look simple. Blessings
Thanks for the info!! Exactly what I needed to learn. Especially at the end about turning it into a table.
I found farmula better than countifs watch and decide
ruclips.net/video/Uw5MiEEA_3A/видео.html
I have a strong understanding of basic spreadsheet formulas, but I've been searching all day today for a formula which will allow me (as a stock market trader) to calculate the average spread (the difference between an asset's sell price and its buy price at the time of purchase), but ONLY with regards to my trades which have ended up being profitable and then I wanted another average spread calculation for trades which were NOT profitable, so which factors in the binary 1's and 0's in my profit and loss columns. You've just solved this problem for me and taught me something extra about spreadsheets. Thank you!
You're very welcome! I hope a lot was in the profitable column :)
Honestly speaking it is the most simplest explanation ever. Thank You. Great video.
You're welcome. Glad you like it.
Thanks Leila....you just made excel sweeter to use!!!
Thank you! Clear instruction, easy to understand and comes with the free workbook! Definitely will help me for tomorrow's lesson! Hope I could get plus score ;")
Many thanks for the explanation @Leila Gharani . I bumped into this when I was looking for a video on sumif. I am faced with a situation and it can be achieved by sumif/sumifs.
An example of the data in Spreadsheet
Date Team Possession
17/Sep/2023 Arsenal 75
17/Sep/2023 Everton 25
21/Sep/2022 Arsenal 59
21/Sep/2022 PSV 41
24/Sep/2022 Arsenal 46
24/Sep/2022 Tottenham Hotspur 54
The first column is Date, the second is team and the 3rd column is Possession. Note that the Date is repeating for 2 rows.
The 2nd column is Team and it gets repeated in alternate rows. The third column is Possession.
The problem statement: I want to create a formula such that whenever I enter a value for field i.e. the possession, the same field for the same date should be auto-populated by (100-the value entered in the other cell) such that the total of the 'Possession' field is 100%.
I think I am able to explain. Could you help me achieve this simple automation ?
Thanks Leila Gharani……. I saw your several videos which connects with excel functions and It has been very helpful to me.
...!
.
You're very welcome. I'm glad the videos are helpful!
Really useful input about SUMIFS statements especially putting quotation marks around > symbols. Thanks
I needed a good explanation on this topic, many thanks Leila.
You’re welcome 😊
Excellent Video Leila, You have a gift of teaching thank you.
Your explanation is simply THE BEST!!
I'm very happy to hear that!
Very soft and polite way of teaching is the best tool for her. Nice and excellent videos on the other topics too.
Thank you! Glad you like the videos :)
You are most welcome. You deserve to be appreciated for your work. It is free for learners. Keep going.
Keep on your excellent work!
1,110 days on from the release of this video, and the fairly recent introduction of dynamic arrays has really got me thinking about how I write formulas!
One of the issues I find with functions like SUMIFS and COUNTIFS is that they don't allow for OR conditions or multiple column arrays. Even doing a conditional SUMPRODUCT with ranges that span over two columns will result in an error. This is where I've found *SUM(IF(…* or *COUNT(IF(…* formulas to be really useful because they provide a lot more flexibility.
I think before - there was a reluctance to use formulas like this because you had to press Ctrl + SHIFT + RETURN. Now it just feels a lot better.
Don't know what you talking about but I will experiment it out
thanks for all the time that you expand for us
Thanks for this value information
very simply explained, very nice
You teach it always so clear in the best timing
Your videos are always very helpful and have always inspired me to learn something new from you. By providing free workbooks to viewers who actually can't afford your course is really appreciable. I'm really thankful to u. :)
You are so welcome!
Thank you for your help. It was just in time!!
Thank you for this lovely explanation.💐
Really your teaching method is excellent..proud of you Leila
Thank you for a great video Leila! I could apply it immediately with success
Perfect!
wish, I found this channel before.. excellent content. Thanks Leila.
Great tutorial, Leila ! Very Nice and informative! Thanks a bunch for sharing!
I was fortunate to find this video on the use of wildcard searches, and used that with your video about the use of COUNTIFS. Thank you!
Very helpful video, thanks Leila.
HEBAT HEBAT!!! Pinter banget. Thank you Leila! Your videos are very helpful. Amazed.
Exactly what i was looking for. Thank you
Really helpful ... Thank you sow Much.
Looking Forward to start Consuming all your Playlists.
Keep it UP 👍
Hi I have watched a few of your video tutorials and think that you are a great teacher. Thank you :)
Thank you very much. I'm glad you like the tutorials. Thank you for your support :)
After watching many videos I finally understood how to use SUMIFS and the logic behind it.Thank you x
Glad to help!
Great learning tips. Thank you so much for providing valuable information. I have been learning from these videos and found it useful.
Really you teaching with easy methods. It's remarkable
Thank you for the efforts, its really helps a lot and easy.
I was here trying to learn SUMIFS but turned out I learned;
- AVERAGEIFS
- COUNTIF
- COUNTIFS
- how to put > or < and " into the formula interchangeably
- and how to transform data into an Excel table with ease
I don't get why a hundred people gave her dislikes but this sure is one of the best Excel how-to video!!
I'm a relative novice to using Excel, and I find these functions to be a bit intimidating to master, HOWEVER, even I find this tutorial quite easy to understand and digest. I just became your subscriber. Thanks for this!
That's great to hear. Thanks for the support. Agree - Excel functions are intimidating at first but they are lots of fun when you get to know them better :)
BTW, technically speaking, "Find out how many shirts were sold for the below criteria:" is incorrect, right? Because you're not trying to figure out the actual amount of shirts that were sold.
Hi - actually yes - we need to count how many were sold here...
Immensely grateful for great learning ❣️❤️
I watch, followed, subscribed and liked your videos. You're such an amazing tutor! Thank you! Muchas gracias!
Thanks for subbing!
Thank you, this is so, so useful