How to Use SUMIFS with Multiple Criteria in Excel and Google Sheets

SUMIFS adds numbers only when every specified condition is met, making it useful for questions such as “How much did the East region sell in January?” The key is matching each criteria range to the sum range and writing each condition in the right form.

SUMIFS syntax and argument order

In Excel and Google Sheets, SUMIFS uses this structure:

=SUMIFS(sum_range, criteria_range1, criterion1, [criteria_range2, criterion2], ...)

• sum_range contains the numbers to add.
• Each criteria_range contains the values to check.
• Each criterion specifies the required value or comparison.

The first argument is the range to sum.

This differs from the older SUMIF function, whose sum range comes last.

Each additional condition is supplied as a range-and-criterion pair.

SUMIFS combines those pairs with AND logic: a row is included only if it satisfies every condition.

Example: Sum sales by region and month

Suppose a worksheet has these columns, with headers in row 1 and records in rows 2 through 100:

• Column A: Region
• Column B: Month
• Column C: Sales

To add sales for the East region in January, use:

=SUMIFS(C2:C100, A2:A100, "East", B2:B100, "January")

The formula checks column A for East and column B for January, then adds the corresponding values in column C.

For example, if the qualifying January sales are $120, $85, and $95, the expected total is $300.

Ranges should cover the same rows.

If the sum range is C2:C100, use criteria ranges such as A2:A100, not A2:A99.

Inconsistent range dimensions can cause errors or unexpected results.

Use cell references for flexible criteria

Hard-coded criteria work for a quick calculation, but references make a formula easier to reuse.

If E2 contains East and F2 contains January, write:

=SUMIFS(C2:C100, A2:A100, E2, B2:B100, F2)

Changing either cell updates the result without editing the formula.

You can also lock ranges when copying a formula down a report.

For instance, use $C$2:$C$100, $A$2:$A$100, and $B$2:$B$100 so the ranges stay fixed while criteria references such as E2 adjust by row.

Combine numeric criteria with comparison operators

Criteria can test whether values are greater than, less than, or equal to a threshold.

If column D contains units sold, this formula totals sales in column C for records with at least 10 units:

=SUMIFS(C2:C100, D2:D100, ">=10")

When the operator is combined with a value stored in a cell, join them with &.

If F2 contains a minimum number of units, use:

=SUMIFS(C2:C100, D2:D100, ">="&F2)

This pattern also works for criteria such as less than a target, not equal to a status, or greater than a date.

Put the comparison operator inside quotation marks; do not type it as an HTML tag.

Sum values between two dates

Dates are stored as serial values in Excel and Google Sheets.

To sum sales in column C for dates in column B from January 1 through January 31, 2026, use two conditions on the date range:

=SUMIFS(C2:C100, B2:B100, ">="&DATE(2026,1,1), B2:B100, "<"&DATE(2026,2,1))

The inclusive start and exclusive next-month boundary include every time on January 31 if column B contains timestamps.

The less-than sign is written as text inside the formula.

If the cells contain dates only, an inclusive end date such as "<="&DATE(2026,1,31) can also be used.

Using DATE(year,month,day) avoids ambiguity between regional date formats, such as month/day/year and day/month/year.

Make sure the criteria range actually contains date values rather than text that merely looks like dates.

Understand AND and OR logic

Separate SUMIFS criteria pairs use AND logic.

For example, a row must be in the East region and have January as its month to be counted.

For OR logic—such as summing East or West sales—add separate SUMIFS results:

=SUMIFS(C2:C100, A2:A100, "East")+SUMIFS(C2:C100, A2:A100, "West")

Another option is to put the alternatives in a small range and use an array-based formula, but support and result behavior can vary by spreadsheet version.

The two-SUMIFS approach is straightforward when the list of alternatives is short.

Avoid adding overlapping conditions if a row could match more than one alternative, because it may be counted twice.

Text matching, wildcards, and exclusions

Text criteria generally ignore capitalization in both Excel and Google Sheets.

For a partial match, use the wildcards * for any number of characters and ? for one character.

To total products whose names contain “coffee,” for example:

=SUMIFS(C2:C100, A2:A100, "*coffee*")

To exclude rows marked “Cancelled” in column B, use:

=SUMIFS(C2:C100, B2:B100, "<>Cancelled")

Use a tilde before a wildcard character when you need to match it literally: ~* matches an asterisk, and ~? matches a question mark.

A blank criterion can be represented by an empty string such as ""; "<>" checks for nonblank cells.

Common SUMIFS problems and fixes

• Wrong argument order: Put the sum range first, followed by criteria-range and criterion pairs.
• Range sizes do not match: Keep each range aligned to the same rows and columns.
• Numbers or dates are stored as text: Convert the source data to real numeric or date values before relying on comparisons.
• Unexpected zero result: Check for extra spaces, misspellings, mismatched data types, and criteria that are more restrictive than intended.
• Operator is treated literally: Build a dynamic comparison with a quoted operator joined to its cell reference, such as ">="&F2.
• Added rows are omitted: Expand fixed ranges or use Excel table references so formulas include new records.

SUMIFS is available in modern desktop versions of Excel, Excel for the web, and Google Sheets.

It is supported in Excel 2007 and later, including Microsoft 365.

Older Excel releases, such as Excel 2003, do not include SUMIFS; use a compatible alternative such as a carefully constructed SUMPRODUCT formula.

Google Sheets also supports the standard SUMIFS syntax, though array behavior and locale-specific separators can differ across spreadsheet environments.