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)
Excel SUMIFS function

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)
Excel SUMIFS function multiple criteria

The SUMIFS formula sum the values based on the given conditions: $492 + $591 + $535 = $1,618.

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

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.

Excel SUMIFS function with wildcards

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.

Excel Download Practice file

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.