How to Use SUMIFS, COUNTIFS and AVERAGEIFS in Excel (Multiple Criteria)
ฝัง
- เผยแพร่เมื่อ 21 ก.ค. 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
![[TH] VCT Pacific Stage 2 - Playoffs Lower Bracket Final // PRX vs DRX](http://i.ytimg.com/vi/5HwO1bQyAx4/mqdefault.jpg)
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
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
th-cam.com/video/Uw5MiEEA_3A/w-d-xo.html
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.
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!
Excellent tutor, you explain fumula so clearly thank you Leila!
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!
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 !
you saved me
ill watch all the rest
your way is simple to understand
Excellent tutor, made things very simple and easy, thank you!
Very happy to hear that Jerry. You are very welcome.
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...
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.
Hi Leila, really I am a fan of your videos and techniques, and keep watching...thank you
Great video! SUMIFS and the like are so powerful!!! And fun too : )
So True! Thank you Mike.
Thank you Leila for supporting us with your great knowledge
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!
This is a great refresher! Thank you, Leila!
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.
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
First of all thank you for your precious lectures on this topic, ma'am, its very helpful. Kindly upload all the series. Thanks
Thank you Leila
Very smart & useful Excel techniques.
You're very welcome Hattem :)
Hattem
THANK YOU
You're very welcome.
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 TH-cam 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!!!❤❤❤
Though Leila's videos are here on TH-cam for years now but I noticed those only today. I don't know how many characters this TH-cam 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 :)
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? 😅
@Leila Gharani You have covered subtle details and possible mistakes in very simplified way, it saved my day.
Great job and Thanks
I’ve used the SUMIFS function before but just got stuck this morning. This refresher unblocked my brain! Thank you!!
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.
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'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.
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!
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 : )
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
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.
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 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.
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!
OMG TQSM!! IVE BEEN SEARCHING MANY VIDEOS TO UNDERSTAND THIS FX FOR WEEKS IN ORDER TO SUBMIT MY PROJECT TO MY LECTURER 😭
Just love the way she explains every formula. thanks a lot.
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 your video. I learn a lot. I recently started using the if function and enjoying it. Let me give you a scenario: A is Low Risk 0 - 19%, B is Lower Risk 20% - 39%, C is Medium Risk 40% - 59%, D is High Risk 60% - 79%, E is very High Risk 80% - 100%. For each one, I do an IF function for True and False. The very formula gives me a "True". I use =. Here is my formula: =IF(AA6
you can go for conditional formatting
Thanks for this quick learning on sumif, sumifs.. I have learnt all in one go without any learning complexity. Thanks
Your tips are awesome, I keep visiting and revisiting when i have complex issues... Thank you
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!!
Leila, you are amazing! Thanks a lot for sharing your knowledge in a very clear way!
finally someone explains it simply! Thank you so much for your time and effort, Miss Gharani!
You're very welcome! Glad it is useful.
Thanks a lot Leila, I liked your explanation very much, specially the reason why we shall not use SUMIF anymore but SUMIFS and the TABLES explanation at the end. Regards from Brazil !
Glad you like it Marcio.
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!
Really your teaching method is excellent..proud of you Leila
Honestly speaking it is the most simplest explanation ever. Thank You. Great video.
You're welcome. Glad you like it.
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!
HEBAT HEBAT!!! Pinter banget. Thank you Leila! Your videos are very helpful. Amazed.
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 :)
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 ?
Very useful for developing habit of reducing manual work and automate task. Thanks Leila. Expecting more from you.
You are welcome 😊
thank you sooo much you have made my life easy i have acomputer application exam tuesday and i had nowhere to begin from
Thanks so much Leila! It is very useful and quickly understand due to your clear way of explaining. Looking forward others sharing from you.
wish, I found this channel before.. excellent content. Thanks Leila.
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 :)
After watching many videos I finally understood how to use SUMIFS and the logic behind it.Thank you x
Glad to help!
Really useful input about SUMIFS statements especially putting quotation marks around > symbols. Thanks
Excellent Tutor. It is easy to understand here, very well explained.
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!
The part of this that helped me was using quotations around the comparison operators. Thanks!
Hie Leila Gharani, your videos are very helpful. Full of content you're merely a humble tutor
Excellent tutorial, I now know how to perform SUMIFS, AVERAGEIFS and COUNTIFS. You helped me to understand the steps to take. Thank You!
I'm glad to hear that Wynne. You're very welcome :)
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!
Thanks Ms Leila. These videos are very useful. Your style of tutoring is superb. To the point. Well designed examples. Namastae.
Glad it was helpful!
Thank you Leila, very useful. Previously I used to implement Index-Match function for this calculation.
Found Farmula better than countifs
th-cam.com/video/Uw5MiEEA_3A/w-d-xo.html
Watching your video even though i know the subject, always add to my knowledge. Excellent example of SUMIFS.
Thanks Isamil. Glad you find the content useful :)
You are the best!!! You're videos never pass out!! Just learn how to condition the formula to a cell...
Thanks Leila for going slow so that the brain can grasp fully, you are the bestest teacher!!
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 :)
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! 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 ;")
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.
Thank you, Leila. Super clearly explained as usual!👋
Really helpful ... Thank you sow Much.
Looking Forward to start Consuming all your Playlists.
Keep it UP 👍
Thank you Leila, you are truly a tutor. You explained so clearly I'm impressed.
Glad you think so!
Excellent Video Leila, You have a gift of teaching thank you.
Thank you Leila, your explaining very clearly and it will be very useful..
Great lesson , many thanks for sharing . If we needed to look at sales for Q1 ( Jan/Feb/March ) how would we look at going about adding that into the formula ? Many thanks
You are awesome - it is so hard to explain this function to my staff, so I decided to find the best video I could on it....after several searches I found you! Thanks for this, it is very good. Subscribed....looking forward to reviewing your channel futher.
Awesome, thank you! Glad to have you here.
High Leila, thank you very much for these awesome videos! I was wondering if there was a way to make the criteria selection box into a drop down menu of the selection. Is there a way?
Great tutorial, Leila ! Very Nice and informative! Thanks a bunch for sharing!
Leila - You are a great teacher. Thank you so much
thank you so much for your lectures, I'm learning so much!
Thanks Leila....you just made excel sweeter to use!!!
Leila, Many thanks for the demystification of this critical functions of the sumif single criteria and the sumifs multiple criteria.
My pleasure. Glad it was helpful.
I watch, followed, subscribed and liked your videos. You're such an amazing tutor! Thank you! Muchas gracias!
Thanks for subbing!
What a useful information, thanks a lot, I will consult this channel again when I have another question
OMG OMG, I CAN'T EXPRESS HOW MUCH YOUR VIDEOS ARE SAVING MY LIFE!!!, EVEN AN OLD VIDEOS, THANK YOU VERY MUCH :)
Happy to hear that!
Very simple and effective example. You saved me.
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
th-cam.com/video/Uw5MiEEA_3A/w-d-xo.html
Thank you so much! One of the best channel on TH-cam.
Many thanks, I found what I was looking for. Your videos are always helpful
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
th-cam.com/video/1abjBrSqTnk/w-d-xo.html
th-cam.com/video/l80mLoaO_-o/w-d-xo.html
th-cam.com/video/UMNOkrAcE8A/w-d-xo.html
th-cam.com/video/SQbRatup2qU/w-d-xo.html
dont forget to like n subscribe.
Thanks Leila, you make this look simple. Blessings