Workbook from the video
Get the workbook from Leila’s video
Enter your email to get the Excel practice file and follow along with the examples.
Send Me Leila’s WorkbookFree download. No spam. Unsubscribe anytime.
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
Free Excel practice file
Get the SUMIFS workbook
Want to practice with the same type of examples from the lesson? Enter your email and I’ll send the workbook to your inbox.
Send Me the WorkbookNo spam. Unsubscribe anytime.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
