Excel SUMIFS Example: Multiple Criteria Explained

Formulas & functions · Updated November 22, 2026

The simplest way to sum values with multiple criteria in Excel is the SUMIFS function, written as =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”, use =SUMIFS(C2:C100, A2:A100, "North", B2:B100, "Q1").

What Is SUMIFS and When to Use It

SUMIFS is a built-in Excel function that adds together numbers in a range only when they meet one or more conditions. It is the multi-criteria version of the older SUMIF, which handles only a single condition. You would use SUMIFS when you need a total that depends on two or more filters, such as summing sales by region and product, expenses by department and month, or hours worked by employee and project. The function works in Excel for Microsoft 365, Excel 2021, Excel 2019, and earlier versions, and the syntax is identical in Excel for the web and Excel for Mac.

How the SUMIFS Syntax Works

The function requires at least one criteria pair, but you can add up to 127 pairs. The arguments are:

  • sum_range – the cells to add (must contain numbers).
  • criteria_range1 – the first range to check.
  • criteria1 – the condition that must be true (text, number, or expression).
  • criteria_range2, criteria2 – optional additional pairs.

Critically, every criteria range must be the same size and shape as the sum_range. If they are not, Excel returns a #VALUE! error. The criteria can be typed directly as a string (e.g., "North"), a number, a cell reference (e.g., E1), or a comparison operator (e.g., ">100").

Worked Example: Summing Sales by Region and Quarter

Assume you have a table with headers in row 1: A1=”Region”, B1=”Quarter”, C1=”Sales”. Data runs from rows 2 to 100. You want total sales for the North region in Q1.

In cell E2, enter:

=SUMIFS(C2:C100, A2:A100, "North", B2:B100, "Q1")

This formula checks each row: if column A equals “North” and column B equals “Q1”, it adds the value in column C. If your data has 5 rows matching, with sales 100, 200, 150, 300, and 250, the result is 1000.

To make the criteria easier to change, put them in cells. For example, put “North” in E1 and “Q1” in F1, then use:

=SUMIFS(C2:C100, A2:A100, E1, B2:B100, F1)

Now you can change the region or quarter without editing the formula.

Using Wildcards and Comparison Operators

SUMIFS supports wildcards for partial text matches:

  • * – matches any sequence of characters. For example, "North*" matches “North” and “Northeast”.
  • ? – matches any single character. "A?" matches “AB” but not “ABC”.
  • ~ – escapes the wildcard, so "~*" matches a literal asterisk.

For numeric conditions, use operators inside the criteria string: ">100", "<50", ">=1000", "<>0". For example, to sum sales over 500 in the North region:

=SUMIFS(C2:C100, A2:A100, "North", C2:C100, ">500")

Notice that the sum_range can also be used as a criteria range. This is perfectly valid.

Common Mistakes and How to Fix Them

Several errors trip up users:

  • #VALUE! error – usually because the criteria ranges are not the same size as the sum_range. Check that all ranges have the same number of rows and columns.
  • Wrong results with blank cells – SUMIFS treats blank cells as zero only if the criteria is a number. If you want to sum where a cell is blank, use "=" as the criteria (e.g., SUMIFS(C2:C100, B2:B100, "=")). For non-blank, use "<>".
  • Text vs. number mismatch – If your criteria looks like a number but is stored as text, SUMIFS may not match. Convert the criteria cell to a number or use the VALUE function.
  • Case sensitivity – SUMIFS is not case-sensitive, so "north" and "NORTH" match. If you need case-sensitive matching, use SUMPRODUCT with EXACT.

Alternatives to SUMIFS

While SUMIFS is the standard, other functions can handle multiple criteria:

  • SUMPRODUCT – more flexible for complex logic, e.g., =SUMPRODUCT((A2:A100="North")*(B2:B100="Q1")*C2:C100). It can handle arrays and even OR logic, but it is slower on large datasets.
  • FILTER (Microsoft 365 or Excel 2021) – first filter the data, then sum. Example: =SUM(FILTER(C2:C100, (A2:A100="North")*(B2:B100="Q1"))). This is more readable but requires a newer Excel version.
  • SUMIF – only for one criterion. Use it when you have just one condition.
  • DSUM – a database function that uses a separate criteria range, which is useful when you want to change criteria without editing formulas.

For most everyday tasks, SUMIFS is the best balance of simplicity and power.

How to Choose Between SUMIFS and SUMPRODUCT

Use SUMIFS when you have straightforward AND conditions (all criteria must be true) and your data is not extremely large. It is faster and easier to read. Use SUMPRODUCT when you need OR logic (e.g., sum if region is "North" OR "South") or when you need to multiply values before summing (e.g., sum of price times quantity). SUMPRODUCT can also handle arrays without needing to press Ctrl+Shift+Enter in older versions, but it calculates more slowly on big ranges. If you have Excel 365, FILTER is often clearer for complex conditions.

Frequently Asked Questions

Can SUMIFS sum based on dates? Yes. Use the date as a criteria, either as a text string in a format Excel recognizes (e.g., "1/1/2024") or as a cell reference containing a date. For multiple date conditions, use operators like ">="&DATE(2024,1,1).

How do I sum with OR logic in SUMIFS? SUMIFS only does AND logic. For OR, you can add two SUMIFS together, e.g., =SUMIFS(C2:C100, A2:A100, "North") + SUMIFS(C2:C100, A2:A100, "South"), or use SUMPRODUCT with + for OR.

Why is my SUMIFS returning 0? Common reasons: the criteria text has extra spaces, the criteria range includes header text, or the numbers are stored as text. Check for trailing spaces using TRIM, and ensure the criteria exactly matches the cell content.

Can SUMIFS reference another sheet? Yes. Use the sheet name followed by an exclamation mark, e.g., =SUMIFS(Sheet2!C2:C100, Sheet2!A2:A100, "North"). If the sheet name has spaces, enclose it in single quotes: 'My Sheet'!C2:C100.

Does SUMIFS work with whole columns? It can, e.g., =SUMIFS(C:C, A:A, "North"), but this can slow down calculation because it processes a million rows. It is better to limit the range to your actual data.

How do I sum cells that are not blank? Use the criteria "<>" (not equal to empty). For example, =SUMIFS(C2:C100, B2:B100, "<>") sums C where B is not blank.

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.