Grab our practice workbook 👉 HERE and follow along:
SUMIFS helps you sum values in a range based on several conditions. It’s perfect for scenarios like adding sales figures for specific products or regions.
How to Use SUMIFS in Excel
Here’s the SUMIFS syntax:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)- sum_range: Cells to add.
- criteria_range1: Range for the first condition.
- criteria1: The first condition.
- criteria_range2: (Optional) Range for the second condition.
- criteria2: (Optional) The second condition.
SUMIF VS SUMIFS
The SUMIF function in Excel handles only one criterion. But with the SUMIFS you can easily handle multiple criteria. You can add up to 127 pairs of criteria ranges and criteria in a single SUMIFS function. This flexibility allows for detailed and complex data analysis.
Discover how to use the SUMIF function in Excel with our detailed guide.
Basic SUMIFS Excel
In the example below, we want to find the total sales amount for “Shirt white” in February, using the sales data in column C.
Formula
=SUMIFS(D3:D21, A3:A21, F4, B3:B21, G4)Explanation
- D3:D21: This is the range of cells containing the sales amounts you want to sum. (This is called the sum_range)
- A3:A21: This is the range of cells containing the months. (This is called the criteria_range1)
- F4: This is the cell with the first criteria, “Feb,” which we want to match. (This is called criteria1)
- B3:B21: This is the range of cells containing the product names. (This is called the criteria_range2)
- G4: This is the cell with the second criteria, “Shirt white,” which we want to match. (This is called criteria2)
The SUMIFS formula sum the values based on the given conditions: $492 + $591 + $535 + $410 = $2,028.
Adding Another Condition
If you have another condition, you can easily expand the SUMIFS formula. For example, if you also want to filter by country:
=SUMIFS(D3:D21, A3:A21, F4, B3:B21, G4, C3:C21, H4)
The SUMIFS formula sum the values based on the given conditions: $492 + $591 + $535 = $1,618.
Featured Course
Master Excel’s Essential Modern Functions
SUMIFS Using Wildcards
Wildcards are useful for partial matches. For instance, if you want to sum sales for products that start with “Shirt”, you can use the * wildcard:
=SUMIFS(D3:D21, D3:D21, "<"&G4)
In this case, the * wildcard matches any number of characters following “Shirt”. This means “Shirt blue”, “Shirt white”, and “Shirt yellow” would all be included in the sum.
Similarly, let’s say you want to sum sales for products that have names where the second character is “h“. For example, you want to include products that will match “Shirt” but not “Skirt”:
=SUMIFS(D3:D21, B3:B21, "?h*")SUMIFS Using Logical Operators
💡 When using logical operators like ‘<‘, ‘>‘, or ‘=‘ with a cell reference, you need to combine them with correctly. This is called concatenation. In our example, the ‘less than’ symbol should be in quotes (“<“), but the ampersand (&) and the cell reference (G4) should not.
Common Errors
- Mismatched Ranges: Ensure all ranges are the same size. If sum_range has 10 cells, criteria_range should also have 10 cells. Mismatched sizes can cause incorrect results or errors.
- Wrong Data Types: Criteria must match the data type of the range. For example, if the criteria range contains text, your criteria should also be text. Using a number for a text range will result in an error.
Tips for Using SUMIFS
- Wildcards: Use * for multiple characters and ? for one character.
- Logical Operators: Use “>”, “<“, “>=”, or “<=” for numbers.
- Cell References: Use cell references for criteria to make formulas adaptable.
Download the Workbook
Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel SUMIFS Function with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
