Excel SUMIFS with Multiple Criteria Explained

Formulas & functions · Updated November 22, 2026

Yes, Excel’s SUMIFS function is specifically designed to sum values that meet multiple criteria simultaneously. For example, =SUMIFS(C2:C10, A2:A10, "North", B2:B10, "Q1") adds all numbers in C2:C10 where column A equals “North” AND column B equals “Q1”. This article explains how SUMIFS works, how to use it with text, numbers, dates, and wildcards, and how to troubleshoot common errors.

What Is SUMIFS and How Does It Differ from SUMIF?

SUMIFS was introduced in Excel 2007 as an upgrade to SUMIF. The key difference is argument order and capacity. SUMIF takes a single criteria range and a single condition: =SUMIF(A2:A10, "North", C2:C10) sums C2:C10 where A2:A10 equals “North”. SUMIFS takes the sum range first, then pairs of criteria ranges and criteria: =SUMIFS(C2:C10, A2:A10, "North", B2:B10, "Q1"). You can include up to 127 range/criteria pairs in SUMIFS, while SUMIF only supports one. For a single condition, both work, but SUMIFS is often preferred because its syntax is more consistent when you later add more criteria.

How to Write a SUMIFS Formula: Step-by-Step

Assume you have a table with sales data: column A contains region names (A2:A20), column B contains product names (B2:B20), column C contains sales amounts (C2:C20). To sum sales for “North” region and “Widget” product, follow these steps:

  1. Click an empty cell, for example E2.
  2. Type =SUMIFS(.
  3. Select the sum range C2:C20, then type a comma.
  4. Select the first criteria range A2:A20, type a comma, then type the criteria "North" (or reference a cell containing “North”).
  5. Type a comma, select the second criteria range B2:B20, type a comma, then type "Widget".
  6. Close the parenthesis and press Enter.

The formula looks like: =SUMIFS(C2:C20, A2:A20, "North", B2:B20, "Widget"). If the table has 50 for North/Widget, 30 for North/Gadget, and 20 for South/Widget, the result is 50 because only rows matching both conditions are summed.

Using Cell References, Wildcards, and Operators in Criteria

Instead of hardcoding text, you can reference cells. For example, if E1 contains “North” and F1 contains “Widget”, use =SUMIFS(C2:C20, A2:A20, E1, B2:B20, F1). This makes the formula dynamic. For partial matches, use wildcards: "*North*" matches any cell containing “North”; "W?dget" matches “Widget” or “Wudget” (question mark represents one character). To sum values greater than a number, use ">100" as a criterion. For dates, use ">="&DATE(2024,1,1) or reference a cell with a date. For blank or non-blank cells, use "" for empty and "<>" for non-empty. Remember that text criteria are case-insensitive, so “north” and “NORTH” are treated the same.

Common Mistakes and How to Fix Them

Three frequent errors occur with SUMIFS. First, mixing up argument order: the sum range must come first, not last. If you write =SUMIFS(A2:A20, C2:C20, "North"), you are summing region names instead of sales. Second, using a criteria range of a different size than the sum range. All ranges must have the same number of rows and columns; otherwise, Excel returns #VALUE!. For example, summing C2:C20 but criteria range A2:A19 will fail. Third, using comparison operators incorrectly with text. To sum sales where region is not “North”, use "<>North" not "!=North". Also, when using operators with cell references, concatenate with an ampersand: ">"&E1 works, but ">E1" treats it as literal text.

SUMIFS with OR Logic (Multiple Criteria in One Column)

SUMIFS inherently uses AND logic: all criteria must be true for a row to be included. To sum values that meet any of several criteria (OR logic), you have a few options. One is to add multiple SUMIFS together: =SUMIFS(C2:C20, A2:A20, "North", B2:B20, "Widget") + SUMIFS(C2:C20, A2:A20, "South", B2:B20, "Widget"). This works but gets long. Another method uses SUMPRODUCT: =SUMPRODUCT(C2:C20 * ((A2:A20="North") + (A2:A20="South")) * (B2:B20="Widget")). This multiplies the sum range by a 1 or 0 for each condition. A third, more modern approach is to use the FILTER function (Excel 2021 or Microsoft 365): =SUM(FILTER(C2:C20, ((A2:A20="North") + (A2:A20="South")) * (B2:B20="Widget"))). FILTER returns an array of matching values, and SUM adds them. Choose based on your Excel version and preference.

Comparing SUMIFS with SUMPRODUCT and FILTER

SUMIFS is the fastest and most readable for AND logic with multiple columns. SUMPRODUCT is more flexible: it can handle OR logic, complex conditions, and even array calculations without requiring Ctrl+Shift+Enter. However, SUMPRODUCT can be slower on large datasets (tens of thousands of rows) and its syntax is less intuitive. FILTER is part of the dynamic array functions and is excellent for returning results that spill into multiple cells, but it requires Excel 2021 or Microsoft 365. For simple AND criteria, SUMIFS is the recommended choice because it is purpose-built and efficient. For OR logic, SUMPRODUCT is a solid fallback if you don’t have dynamic arrays.

Performance and Large Data Sets

When working with thousands of rows, SUMIFS is generally fast because it is optimized by Excel. However, using whole-column references like A:A can slow down calculations because Excel processes all 1,048,576 rows. Instead, limit ranges to the actual data, e.g., A2:A10000. Also, avoid volatile functions like OFFSET inside SUMIFS. If you need to sum based on many criteria (more than 10), consider using a PivotTable or Power Query, which can handle large data more efficiently. For real-time calculations, keep your data in an Excel Table (Insert > Table) and use structured references, which automatically adjust as you add rows.

Frequently Asked Questions

Can SUMIFS sum with multiple criteria in the same column?

No, SUMIFS uses AND logic, so if you specify the same column twice with different criteria, no row can match both. For example, =SUMIFS(C2:C10, A2:A10, "North", A2:A10, "South") returns 0 because a cell cannot be both. To sum for either North or South, use SUMPRODUCT or add two SUMIFS together.

How do I use SUMIFS with dates between two dates?

Use two criteria on the same date column: =SUMIFS(C2:C20, A2:A20, ">="&DATE(2024,1,1), A2:A20, "<="&DATE(2024,12,31)). This sums where the date in A2:A20 is on or after Jan 1, 2024, and on or before Dec 31, 2024. Ensure the date cells are formatted as dates, not text.

Why does my SUMIFS return 0 even though there are matches?

Check for leading/trailing spaces in criteria cells, or text that looks like numbers. Use =TRIM(A2) to clean data. Also verify that the criteria range and sum range are the same size, and that you are using the correct argument order. If you have numbers stored as text, SUMIFS may ignore them; convert them to numbers using the Text to Columns wizard.

Can I use SUMIFS with wildcards for partial text matches?

Yes, use asterisk (*) for multiple characters and question mark (?) for a single character. For example, =SUMIFS(C2:C20, A2:A20, "*North*") sums all rows where column A contains "North" anywhere. To match a literal asterisk, use a tilde (~) before it: "~*".

Does SUMIFS work in Excel for Mac and Excel for the web?

Yes, SUMIFS is available in all modern versions of Excel for Windows, Mac, and Excel for the web. The syntax is identical. On Mac, you can use the same formula; keyboard shortcuts differ, but the function works the same. In Excel for the web, you can enter the formula directly, and it will calculate in the browser.

What is the maximum number of criteria in SUMIFS?

Excel allows up to 127 range/criteria pairs in a single SUMIFS formula. In practice, you rarely need that many, but the limit is generous. If you exceed it, Excel returns an error.

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.