What is SUMIFS in Excel?

The SUMIFS function adds numbers only when all specified conditions are satisfied. For example, you can calculate laptop sales in the East region, total expenses for a particular employee and department, or sales recorded between two dates.

SUMIFS is different from SUMIF: SUMIF checks one condition, while SUMIFS can check several. It is available in modern Excel versions, including Excel for Microsoft 365.

SUMIFS syntax

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

• sum_range: Cells containing the numbers to add.
• criteria_range1: Cells checked for the first condition.
• criteria1: The condition, such as "East" or ">1000".
• criteria_range2, criteria2: Optional additional range/condition pairs.

Important: The sum range comes first in SUMIFS, unlike the argument order in SUMIF.

Example data: Regional sales

Enter this data in A1:D7 in Excel. The date column should contain real Excel dates, not text.

Example 1: SUMIFS with two text criteria

To calculate the value of Laptop sales made in the East region, enter:

=SUMIFS(D2:D7,B2:B7,"East",C2:C7,"Laptop")

Result: 155000. SUMIFS adds 50000, 60000 and 45000 because those three records satisfy both criteria.

Example 2: SUMIFS with a date range

To calculate sales for the East region from 1 January through 15 January 2025:

=SUMIFS(D2:D7,B2:B7,"East",A2:A7,">="&DATE(2025,1,1),A2:A7,"<="&DATE(2025,1,15))

Result: 122000. The qualifying sales are 50000, 12000 and 60000. Using DATE() avoids problems caused by regional date formats.

Example 3: Use cell references instead of typing criteria

Enter East in F2 and Laptop in G2, then use:

=SUMIFS(D2:D7,B2:B7,F2,C2:C7,G2)

Result: 155000. Change either input cell and the total recalculates automatically.

Example 4: Greater than or equal to a number

To total East-region sales only for records with an amount of at least 45000:

=SUMIFS(D2:D7,B2:B7,"East",D2:D7,">=45000")

Result: 155000. The East Mouse record of 12000 does not qualify.

SUMIF vs SUMIFS

Common SUMIFS problems and fixes

1. Different-sized ranges

Keep the sum range and every criteria range aligned in shape and dimensions. For the data above, use rows 2 through 7 in each range.

2. Criteria with comparison operators

Wrap an operator and number in quotation marks, such as ">=45000". For a value in cell F2, combine the operator and reference: ">="&F2.

3. Dates stored as text

SUMIFS date comparisons work reliably when cells contain actual numeric Excel dates. Use DATE(2025,1,1) in your formula rather than a date string that Excel might interpret differently.

4. Formula gives zero

Check the spelling of criteria, extra spaces, date types and whether sales amounts are stored as numbers rather than text.

5. OR conditions

SUMIFS combines pairs with AND logic. To calculate East or West sales, you can add two SUMIFS formulas or use a different approach suited to your Excel version.

Practice exercises

1. Calculate the total sales for the West region.
2. Calculate Mouse sales in the West region.
3. Calculate East-region sales after 10 January 2025 (exclusive).

Answers:

=SUMIFS(D2:D7,B2:B7,"West")
=SUMIFS(D2:D7,B2:B7,"West",C2:C7,"Mouse")
=SUMIFS(D2:D7,B2:B7,"East",A2:A7,">"&DATE(2025,1,10))

The results are 58000, 18000 and 105000, respectively.

Frequently asked questions

How many criteria can SUMIFS use?

Microsoft documents support for up to 127 range/criteria pairs, though simpler formulas are easier to maintain.

Does SUMIFS support wildcard characters?

Yes. Use * for any sequence of characters and ? for any single character in appropriate text criteria.

Why is my SUMIFS total incorrect?

Check range alignment, mismatched data types, hidden spaces and comparison operators. Verify a few qualifying rows manually before relying on the result.

Related Excel tutorials

See our Excel Functions learning module and SUMIF tutorial for related topics.

Official reference: Microsoft Support — SUMIFS function.