ExcelMathIntermediate

SUMIFS

Adds cells based on multiple conditions.

Read the syntaxReview worked examplesOpen the spreadsheet app
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Open Spreadsheet Explorer

This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.

What the function does

Overview

Adds cells based on multiple conditions.

SUMIFS calculates the sum of cells in a range that meet multiple criteria. Unlike the older SUMIF function, the ange is the first argument, followed by ange/criteria pairs. A strict technical requirement is that all anges must have the same number of rows and columns as the ange; otherwise, the formula will trigger a #VALUE! error. SUMIFS supports logical operators (>, <, <>, =) and wildcards like '*' for multiple characters and '?' for a single character. For robust spreadsheet design, avoid hardcoding criteria; instead, reference cells and use the ampersand operator to concatenate logic, such as ">"&A1. Note that SUMIFS treats empty cells in the ange as 0 and ignores text strings within the ange during calculation. It is also important to note that criteria are case-insensitive.

Quick reference

Syntax

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

Inputs

Arguments

sum_rangeThe cells to add
Example: C2:C100
criteria_range1Range to check first condition against
Example: A2:A100
criteria1First condition
Example: "East"
criteria_range2 (optional)Range for second condition
Example: B2:B100
Pattern: Criteria Pair

Formula patterns

Examples

1

Summing with multiple text criteria

=SUMIFS(C2:C10, A2:A10, "East", B2:B10, "Widget")
salesregional report
2

Using dynamic cell references with logical operators

=SUMIFS(D2:D100, E2:E100, ">"&A1, F2:F100, "<"&B1)
financedynamic filtering
3

Summing based on wildcard pattern matching

=SUMIFS(B2:B20, A2:A20, "*Dept*", C2:C20, ">0")
accountingstring search

Avoid these issues

Common Errors

1

#VALUE!

Cause: The sum_range and the criteria_ranges do not have the same dimensions (e.g., A1:A10 vs B1:B5).

Fix: Ensure all range arguments cover the exact same number of rows and columns.

2

Zero Results

Cause: Criteria involving numbers or dates are often wrapped entirely in quotes (e.g., ">A1"), causing Excel to look for the literal text "A1" instead of the value in cell A1.

Fix: Concatenate the operator with the cell reference using an ampersand, such as ">"&A1.

Platform support

Compatibility

Excel 2007+Excel-first

Source: Microsoft Support

Common questions

Frequently Asked Questions

Adds cells based on multiple conditions.

ange: The cells to add ange1: Range to check first condition against criteria1: First condition ange2: Range for second condition

#VALUE!: Ensure all range arguments cover the exact same number of rows and columns. Zero Results: Concatenate the operator with the cell reference using an ampersand, such as ">"&A1.