SUMIFS — Conditional Sum with Multiple Criteria

Syntax breakdown

SUMIFS takes three required arguments to start, then any number of additional criteria pairs. The pattern scales cleanly from 1 criterion to 127.

Five working examples

Every example uses a real 24-row sales log from the free workbook. Q1 2026 data across 4 regions, 5 products, and 6 salespeople. Grand total: $125,441.65.

Interactive playground

Common errors and how to fix them

SUMIFS mistakes usually return 0 (or #VALUE!) with no clear error — worse than an explicit error because your dashboard looks like it's working. Seven common scenarios:

SUMIF vs SUMIFS vs SUMPRODUCT — same result, different pattern

Three ways to conditionally sum. Search for any of these comparisons and you'll find this table — here's the real answer:

The IFS family — five functions, one pattern

SUMIFS is the flagship of a family. Once you know the criteria model, four more functions unlock automatically — same argument structure, different aggregation:

Example: to count North-region Widget A transactions instead of summing them, swap the function name — keep everything else identical:

MAXIFS and MINIFS require Excel 2019 or 365 (introduced later than the others). SUMIFS, COUNTIFS, and AVERAGEIFS all ship with Excel 2007+.

Related functions

Excel version compatibility

SUMIFS has been in Excel since 2007. If you're on any modern Excel or spreadsheet app, it just works:

When to use SUMIFS vs. alternatives

Use SUMIFS when…

• You have one or more criteria that must all match (AND logic). This is 95% of real-world conditional sums.
• Your data is in a normal open workbook. Not a closed external file.
• Speed matters. SUMIFS is fast because it's built into Excel's calc engine — much faster than SUMPRODUCT for the same result.
• You want readable formulas. SUMIFS reads almost like English: "Sum amounts where region is North and product is Widget A."

Use SUMPRODUCT instead when…

• You need OR logic across criteria (rows matching Region = "North" OR Region = "South").
• Your criteria involve calculated columns (e.g., LEFT(Product,6) = "Widget").
• You need to sum from a closed external workbook.
• You're stuck on Excel 2003 or older.

Use SUBTOTAL instead when…

• Your users are toggling AutoFilter and you want the total to reflect what's visible.
• You're building a filterable list report where the total updates as filters change.

How SUMIFS actually works

The algorithm

SUMIFS walks through each row of your criteria_ranges in parallel. For every row, it checks: does every criteria pair match? If YES to all criteria, add that row's sum_range value to the running total. If NO to any criteria, skip. It's pure row-by-row filtering with a running sum.

Because all criteria must match (AND logic), the order of criteria pairs doesn't affect the result. But it does affect readability — put your most restrictive criteria first for clearer intent, or group them by column type for maintenance.

Text criteria — the built-in behaviors

• Case-insensitive: "NORTH" matches "north".
• Wildcards work in exact-match mode: * for any characters, ? for one.
• Whitespace is significant — trailing spaces silently break matches. Use TRIM() to clean.

Numeric criteria — the operator syntax

Numbers can be compared with >, <, >=, <=, <>, or omitted (implicit equality). Rules:

• Operators must be INSIDE the criteria string: ">100", not >100.
• To reference a cell in a comparison, use & to concatenate: ">"&C2.
• Implicit equality on a number just uses the number: SUMIFS(Amount, Qty, 100).

Date criteria — dates are numbers under the hood

Excel stores dates as serial numbers (Jan 1, 1900 = 1). So date comparisons work exactly like numeric comparisons — but you'll almost always use cell references or DATE() functions rather than raw serial numbers:

This is Excel's most common date-range pattern. Two criteria on the same date column, one with >= and one with <=, both concatenated to actual date values.

The 127-criteria limit

SUMIFS accepts up to 127 criteria_range/criteria pairs. Realistically nobody uses more than 4-5. If you find yourself approaching double digits, you're probably solving the wrong problem — reshape your data into a proper table and use a PivotTable or Power Query instead.

Performance notes

SUMIFS is one of Excel's fastest aggregation functions. Three things affect its speed:

• Range size. The narrower your ranges (avoid whole-column references like A:A unless truly needed), the faster the calc. Prefer $A$2:$A$5000 over $A:$A.
• Number of criteria pairs. Each additional pair is another comparison per row. Linear scaling — 3 criteria is 3× slower than 1 for the same row count, roughly.
• Data-type consistency. Mixed numeric/text data in a criteria_range forces Excel to coerce types on every comparison — measurably slower than clean data.

When to switch to a PivotTable or Power Query

If you're writing dozens of SUMIFS formulas to build a summary table (sum by region, by product, by month, in a grid), stop. Build a PivotTable — it's faster, easier to maintain, and refreshes with one click. If your data comes from an external source, Power Query's Group By does the aggregation once and produces a clean table.

How to write a SUMIFS from scratch

1. Identify the sum_range

   What's the column of numbers you want to add up? This is your FIRST argument. Get in the habit — sum first, filters after.

2. Add your first criteria pair

   =SUMIFS(sum_range, criteria_range, criteria). Simple text or numbers work as-is: "North" or 100.

3. Add more criteria as needed

   Each additional filter is another range/criteria pair. All must match — AND logic. Add up to 127 pairs.

4. Use the operator + & trick for numeric or date thresholds

   Wrap comparisons in quotes: ">100". Combine with cell references using &: ">"&C2. For dates: ">="&DATE(2026,1,1).

5. Test with known-answer rows

   Before trusting the result, verify against a small subset you can manually verify. SUMIFS returns 0 silently on many errors — always sanity-check.

Functions used with SUMIFS

Frequently asked questions

Templates that use SUMIFS

Of the 10 free templates on Sheets & Cells, 8 use SUMIFS as a core aggregation function. The green tag marks which: