How to do SUMIF in Excel with Multiple Columns
SUMIF in Excel
The SUMIFS function in Microsoft Excel is a powerful tool for adding up numbers based on multiple criteria, expanding on the capabilities of the older SUMIF function, which only allows for a single criterion. This tutorial guides you through using SUMIF and SUMIFS, demonstrating their application in various scenarios.
The overview begins with a basic introduction to the SUMIF function and then progresses to more complex examples using SUMIFS, incorporating multiple criteria, hard-coded values, text, and logical operators.
This function is handy for anyone who needs to analyze data by summing up values that meet specific conditions, making it invaluable for finance, data analysis, and reporting tasks.
- Merging Multiple Files
- SUMIF Function
- SUMIFS Function with Multiple Criteria
- Hard-Coded Values and Multiple Criteria
- Using Logical Operators in SUMIFS
- Combining Multiple Criteria and Logical Operators
SUMIF Function
We start by explaining the SUMIF function, which is used when you want to sum values in a range that meets a single criterion. For instance, if you have a list of items and their prices, you can use SUMIF to calculate the total price for all items of a specific type, like “skirts.” The function requires three main arguments: the range to evaluate, the criteria to match, and the range to sum. This is demonstrated by summing the prices of all skirts in a dataset, which results in a total value verified through manual checking.
SUMIFS Function with Multiple Criteria
Next, we introduce you to the SUMIFS function, which extends the functionality of SUMIF by allowing multiple criteria. In the example, the function is used to sum the prices of skirts that are also a specific color, such as “gray.” Unlike SUMIF, where the sum range is specified at the end, SUMIFS requires it to be defined at the beginning. This section emphasizes how SUMIFS is especially useful when refining your summation based on multiple conditions, allowing for more precise data analysis.
Hard-Coded Values and Multiple Criteria
This tutorial then demonstrates how to use SUMIFS with hard-coded values instead of cell references and how to incorporate multiple criteria, such as summing the revenue for all “blouses” that are “blue” and “size large.” This approach shows the flexibility of SUMIFS in different scenarios, including those where the criteria are directly entered into the formula rather than being selected from cells.
Using Logical Operators in SUMIFS
In a more advanced example, we cover using logical operators in SUMIFS. This is particularly useful when you need to sum values that not only meet specific text or numerical criteria but also exceed a certain threshold. For instance, the function is used to calculate the revenue for all blouses where the profit is greater than a certain amount. This tutorial explains how to incorporate logical conditions within the SUMIFS function by placing them in double quotes, enhancing its capability to handle more complex conditions.
Combining Multiple Criteria and Logical Operators
Finally, we conclude this tutorial with a comprehensive example that combines multiple criteria and logical operators. You’ll learn how to sum the total revenue for items that match three specific criteria and meet a profit condition. This example illustrates the full potential of SUMIFS, showing how it can handle up to five criteria and how each condition refines the data set to provide a highly targeted sum.
Also read:
How to Use VLOOKUP to Compare Two Lists
Please visit our free resources center for more high-quality guides and training.
Ready to dive deep into Excel? Click here for basic to advanced Excel courses with in-depth training modules.
Simon Sez IT has been teaching Excel and other business software for over ten years. You can access 200+ IT training courses for a low monthly fee.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
