Are you looking to level up your Excel skills by learning how to use the SUMIFS function? Whether you’re handling sales data, tracking expenses, or managing project figures, the SUMIFS function can help you filter, and sum values based on multiple criteria. In this post, we’ll break down the SUMIFS function in Excel and provide an example to show exactly how it works. Let’s dive right in!
What Is the SUMIFS Function in Excel?
The SUMIFS function is a powerful tool in Excel that allows you to sum values based on multiple conditions. This is super handy when you’re dealing with large datasets and need to quickly sum up values based on specific criteria.
For example, if you have sales data and want to sum sales for a particular city and salesperson, SUMIFS will be your go-to formula!
Why Use SUMIFS?
- Multiple Conditions: Unlike the basic SUMIF, SUMIFS allows you to apply more than one condition.
- Saves Time: You don’t need to manually filter data before calculating sums.
- Accurate Results: By setting clear conditions, you get precise sums tailored to your needs.
Understanding the Syntax of SUMIFS
Here’s the syntax for the SUMIFS function:
=SUMIFS (sum range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
- sum range: The range of values to sum (e.g., sales figures).
- criteria_range1: The range that will be tested against your first condition.
- criteria1: The actual condition for your first criteria.
- You can continue adding more criteria ranges and conditions as needed.
Now, let’s see how to use this function with a real-world example.
Example: Using SUMIFS to Calculate Total Sales for Specific Names and Cities
We’ve got a data set that contains three columns: Name, City, and Sales. Our goal is to calculate the total sales for a particular combination of Name and City. Here’s what our data looks like:
Let’s say we want to find the total sales for Ela Pitt in Iceland.
Using the SUMIFS Formula
Here’s the formula we’ll use:
=SUMIFS (C5:C24, A5:A24, E5, B5:B24, F5)
This formula tells Excel to sum the sales values (range C5 where the Name (range A5)
matches the value in cell E5 (Ela Pitt), and the City (range B5)) matches the value in cell F5 (Iceland).
The Output
After applying the formula, you’ll get the total sales for Ela Pitt in Iceland:
Isn’t that great? With just one formula, you’ve pulled specific sales data based on multiple conditions.
When to Use SUMIFS
SUMIFS is perfect for situations like:
- Tracking sales: Filter sales based on product, region, or salesperson.
- Expense reports: Sum expenses by category and department.
- Project management: Calculate hours spent on specific tasks for a particular team member.
Common Mistakes to Avoid with SUMIFS
Even though SUMIFS is straightforward, there are a few common mistakes you might want to avoid:
- Mismatched ranges: Make sure your sum range and criteria ranges are the same size. If they aren’t, Excel will return an error.
-
Blank criteria: If a cell in the criteria range is blank, Excel will ignore that condition. Double-check your data to avoid mistakes.
Conclusion
The SUMIFS function in Excel is a game-changer when you need to sum data based on multiple criteria. Whether you’re dealing with small or large datasets, mastering this function will undoubtedly make your work faster, easier, and more accurate.
Visit our YouTube channel to learn step-by-step video tutorials
View this post on Instagram
Click hare to download the practice file
