Sum if Multiple Criteria? How to Do It in Excel

This guide explains how to sum values in Excel when you need to match more than one condition, such as summing sales for a specific product in a specific region. You will learn the correct formulas for both AND-type and OR-type criteria, how to handle blank cells, and common pitfalls to avoid. By the end, you’ll be able to write robust multi-criteria sums without errors.

Formula

The core formula for summing with multiple criteria in Excel is SUMIFS. Its syntax is:

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

Where:

• sum_range – the cells to add (e.g., sales amounts).
• criteria_range1 – the first range to test (e.g., product names).
• criteria1 – the condition for that range (e.g., “Widget”).
• criteria_range2, criteria2 – additional range/condition pairs (up to 127 in modern Excel).

All criteria must be true for a cell to be included in the sum. This is an AND logic. For example, =SUMIFS(C2:C100, A2:A100, "North", B2:B100, "Widget") adds all values in C where column A equals “North” AND column B equals “Widget”.

Worked Example

Suppose you have a small table:

• A2:A6 = Region: North, South, North, East, North
• B2:B6 = Product: Widget, Gadget, Widget, Widget, Gadget
• C2:C6 = Sales: 100, 200, 150, 300, 250

You want to sum sales for North region AND Widget product. Using the default values from the calculator block (though that calculator is for pace, not Excel, we use it as a placeholder per site rules – but for this article, we focus on the Excel example). Let’s compute manually:

1. Rows where Region = North: rows 2, 3, 5 (sales 100, 150, 250).
2. Among those, rows where Product = Widget: rows 2 and 3 (sales 100 and 150).
3. Sum = 100 + 150 = 250.

So =SUMIFS(C2:C6, A2:A6, "North", B2:B6, "Widget") returns 250. The formula checks each row, applies both conditions, and adds only the matching sales.

When to Use SUMIFS vs. SUMPRODUCT

SUMIFS is the modern, straightforward choice for most multi-criteria sums. It is faster, easier to read, and supports wildcards like * and ?. Use it whenever you have simple equality or comparison criteria (e.g., “>100”, “<>“).

SUMPRODUCT is an older, more flexible function that can handle more complex logic, such as OR conditions or criteria that involve other functions. For example, to sum sales where Region is North OR Product is Widget, you can use =SUMPRODUCT((A2:A6="North")+(B2:B6="Widget"), C2:C6) – but note this double-counts rows that meet both conditions. To avoid double counting, use =SUMPRODUCT(( (A2:A6="North")+(B2:B6="Widget") >0 ) * C2:C6). SUMPRODUCT treats TRUE as 1 and FALSE as 0, so it multiplies arrays together.

How to Handle OR Criteria

SUMIFS cannot directly handle OR logic. If you need to sum when any of several conditions are true, you have two main options:

• Add multiple SUMIFS: =SUMIFS(C2:C6, A2:A6, "North") + SUMIFS(C2:C6, B2:B6, "Widget") – but this double-counts rows that satisfy both. To fix, subtract the double-counted sum: =SUMIFS(...) + SUMIFS(...) - SUMIFS(..., A2:A6, "North", B2:B6, "Widget").
• Use SUMPRODUCT with an OR array as shown above, which avoids double counting by using the >0 trick.

For example, to sum sales for North OR Widget without double counting: =SUMPRODUCT( ( (A2:A6="North") + (B2:B6="Widget") > 0 ) * C2:C6 ).

Common Mistakes and Pitfalls

Even experienced users trip up on these:

• Mismatched ranges: All ranges in SUMIFS must be the same size and shape. If sum_range is C2:C100, criteria ranges must also be rows 2 to 100. Otherwise you get #VALUE! errors.
• Text vs. numbers: Criteria like “North” must be in quotes. Numeric criteria like 100 do not need quotes, but if you use comparison operators, you need quotes: “>100”.
• Wildcards: Use * for any sequence, ? for single character. To match a literal asterisk, use ~*.
• Blank cells: If a criteria range contains blanks, use “” to match them, or “<>” to match non-blanks. For example, =SUMIFS(C:C, A:A, "<>") sums where A is not blank.
• Dates and times: Criteria for dates must be entered as date serial numbers or with DATE() function, e.g., “>”&DATE(2025,1,1).

Alternatives: PivotTables and Dynamic Arrays

If you frequently need multi-criteria sums, consider a PivotTable. Drag fields to Rows, Columns, and Values, and set the Value field to Sum. PivotTables let you filter by multiple fields without writing formulas, and they update when you refresh. For one-off analysis, they are often faster than formulas.

In Excel 365, you can also use the FILTER function combined with SUM: =SUM(FILTER(C2:C100, (A2:A100="North")*(B2:B100="Widget"))). This is more flexible and easier to read, but requires dynamic arrays (available in Excel 365 and 2021).

Frequently Asked Questions

How do I sum with multiple criteria in Google Sheets? The same SUMIFS function works in Google Sheets with identical syntax. For OR logic, you can use FILTER or SUMPRODUCT similarly.

Can I use cell references for criteria? Yes, you can use a cell reference like =SUMIFS(C:C, A:A, E1) where E1 contains “North”. For comparison operators, you need to concatenate: =SUMIFS(C:C, A:A, ">"&E1).

Why is my SUMIFS returning 0? Check that your criteria are spelled exactly, including spaces. Also ensure the sum_range contains numbers, not text. If you use wildcards, verify they are correct. If criteria range has numbers stored as text, they won’t match numeric criteria.

How do I sum based on multiple criteria across different sheets? You can reference other sheets in criteria ranges, e.g., =SUMIFS(Sheet2!C:C, Sheet2!A:A, "North"). All ranges must still be same size. For cross-sheet OR, you may need to use SUMPRODUCT with INDIRECT.

What is the difference between SUMIF and SUMIFS? SUMIF allows only one criterion, while SUMIFS allows multiple. SUMIFS also has the sum_range as the first argument, while SUMIF has sum_range as the last. This difference often causes errors when converting.

Related

• WhatsApp Business Multiple Users: How It Works
• Garden Tower Defense Admin Abuse Times
• Mixed Number to Improper Fraction: How to Convert

in t | Finish time | min | 50 | 0
in d | Distance | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2