How to Use SUMIFS, COUNTIFS and AVERAGEIFS in Excel (Multiple Criteria)

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.

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.

🚩Let’s connect on social:

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

[vanesacova]

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!

[thedr]

I've been struggling with SUMIF(s) all morning, this sorted my issue out immediately, thank you for sharing your knowledge!

[kebincui]

Excellent tutor, you explain fumula so clearly thank you Leila!

[ceeroar]

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.

[excelisfun]

Great video! SUMIFS and the like are so powerful!!! And fun too : )

[jerryjulius]

Excellent tutor, made things very simple and easy, thank you!

[emilycestari]

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!

[atulchauhan]

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.

[Xrayankhan]

you saved me
ill watch all the rest
your way is simple to understand

[nelly]

This is a great refresher! Thank you, Leila!

[prerna]

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 !

[ShivaPremi]

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

[shafeektb]

Hi Leila, really I am a fan of your videos and techniques, and keep watching...thank you

[shivamanhas]

Thanks Mam for sharing ur knowledge of Excel with us so we can too Excel in Excel.Thaks a lot.... and keep sharing....

[mohamedgroup]

Thank you Leila for supporting us with your great knowledge

[pauls]

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.

[hattemghanoom]

Thank you Leila
Very smart & useful Excel techniques.

[Mdkhalik]

First of all thank you for your precious lectures on this topic, ma'am, its very helpful. Kindly upload all the series. Thanks

[softambientmusic]

You have a gift! You make the seemingly complicated, remarkably simple. Now demystified Sumifs, countifs, Averageifs for all of us. Thanks Leila!

[s.b.]

i don't trust people who can work with excel