Excel SUMIFS with Multiple Criteria: How To

Formulas & functions · Updated November 22, 2026

The Excel SUMIFS function sums values that meet multiple criteria, and its syntax is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...). For example, to sum sales in column C where column A equals “North” and column B equals “Q1”, you would write =SUMIFS(C2:C100, A2:A100, "North", B2:B100, "Q1"). This article explains how to build such formulas, handle common errors, and use alternatives when your needs go beyond basic criteria.

Background: Why SUMIFS Exists

The older SUMIF function (available since Excel 2007) only handles one condition. To sum with two or more conditions, users had to use array formulas or helper columns, which were slow and error-prone. SUMIFS was introduced in Excel 2007 to handle multiple criteria natively. It is part of the IFS family (along with COUNTIFS and AVERAGEIFS) and works in all modern versions of Excel, including Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel for the web. On Mac, the function works identically, but note that the argument order is the same as Windows—the sum range comes first, not last.

How SUMIFS Works: Syntax and Logic

The function uses AND logic: every condition must be true for a cell in the sum range to be included. The syntax is:

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

Key points:

  • sum_range – the cells to add (must be the same size and shape as each criteria range).
  • criteria_range1, criteria1 – the first pair; you can add up to 127 pairs in modern Excel.
  • Criteria can be numbers, text, cell references, or expressions like ">10" or "*East*".
  • If a criteria range cell is empty, it is treated as 0 (zero) for numeric comparisons, but for text criteria, empty cells are not matched unless you use "".

Example: With data in A2:A20 (region), B2:B20 (quarter), and C2:C20 (sales), to sum sales for “North” in “Q1”:

=SUMIFS(C2:C20, A2:A20, "North", B2:B20, "Q1")

If the data has 5 rows for North-Q1 with sales 10, 20, 30, 40, 50, the result is 150.

How to Choose Between SUMIF, SUMIFS, and SUMPRODUCT

Use SUMIF for a single condition. Use SUMIFS for two or more conditions that all must be true (AND logic). Use SUMPRODUCT when you need OR logic (any condition true) or when you need to sum based on conditions that involve other calculations. For example, to sum sales where region is “North” OR “South”, you cannot do that directly with SUMIFS (it uses AND). Instead, you can use:

=SUM(SUMIFS(C2:C20, A2:A20, {"North","South"}))

That array constant works in Excel 365 and Excel 2019+ (it spills or works as an array). Alternatively, use SUMPRODUCT:

=SUMPRODUCT((A2:A20="North")+(A2:A20="South"), C2:C20)

That adds the two boolean arrays (1 for true, 0 for false) and multiplies by sales.

Common Problems and How to Fix Them

Several issues trip up users:

  • Mismatched ranges: All ranges must be the same number of rows and columns. If you use A2:A20 and B2:B21, you get a #VALUE! error.
  • Criteria with text and wildcards: Use asterisk (*) for any sequence and question mark (?) for a single character. To match a literal asterisk, use ~*.
  • Criteria with dates: Write dates as ">=1/1/2023" or reference a cell containing a date. Better, use the DATE function: ">="&DATE(2023,1,1).
  • Criteria with blank cells: To sum when a cell is blank, use "=" (equals nothing) or ""? Actually, use "=" to match truly empty cells, and "<>" to match non-blank cells.
  • Case sensitivity: SUMIFS is not case-sensitive. For case-sensitive matching, use SUMPRODUCT with EXACT.

Comparisons: SUMIFS vs. Other Functions

Here is a quick comparison:

  • SUMIF – one condition, simpler syntax.
  • SUMIFS – multiple conditions, AND logic.
  • SUMPRODUCT – more flexible (OR logic, array calculations) but slower on large datasets.
  • FILTER (Excel 365) – returns an array, then wrap in SUM: =SUM(FILTER(C2:C20, (A2:A20="North")*(B2:B20="Q1"))) – more readable for complex logic.

Alternatives: Using PivotTables and Dynamic Arrays

If your data is large or you need to summarize by many categories, a PivotTable is often easier. Drag fields to Rows/Columns and Values to sum. For dynamic criteria, use the FILTER function (Excel 365/2021) to extract matching rows, then sum. For example, to sum all sales for North in Q1, you could use:

=SUM(FILTER(C2:C20, (A2:A20="North")*(B2:B20="Q1")))

This returns the same result as SUMIFS but is easier to extend to other operations like average or count.

Frequently Asked Questions

Can SUMIFS use OR logic?

No, SUMIFS uses AND logic. To sum with OR, use an array constant as shown above, or use SUMPRODUCT with addition.

How do I sum if a cell contains a specific text?

Use wildcards: =SUMIFS(C2:C20, A2:A20, "*North*") matches any cell containing “North” anywhere.

Why is my SUMIFS returning 0?

Check that criteria ranges and sum range are the same size, that criteria are typed correctly (no extra spaces), and that the data type matches (e.g., text vs. number). Also ensure the criteria range includes the correct column.

Can I use cell references as criteria?

Yes, simply reference a cell: =SUMIFS(C2:C20, A2:A20, E1, B2:B20, F1) where E1 contains “North” and F1 contains “Q1”.

Does SUMIFS work with dates?

Yes, but be careful with date formatting. Use ">="&DATE(2023,1,1) or reference a cell containing the date.

What is the maximum number of criteria in SUMIFS?

In Excel 2007 and later, up to 127 criteria pairs are allowed.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.