SUMIFS

Sum values that meet multiple conditions at once with SUMIFS, Excel's multi-criteria addition function.

Math & Trigonometry
|
Excel 2007+
|
Google Sheets Supported

Spreadsheet editor

Syntax

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

Arguments

Argument Required Description
Sum_range Yes The range of cells that you want to sum based on the provided criteria.
Criteria_range1 Yes The range of cells to be evaluated by the first criterion.
Criteria1 Yes The criterion or condition to be met in Criteria_range1.
Criteria_range2 No Additional range of cells for evaluating a second criterion.
Criteria2 No The criterion or condition to be met in Criteria_range2, and so on for additional ranges and criteria.
... No Additional pairs of ranges and criteria can be added as needed.

About

SUMIFS adds values in a range only when every condition you specify is true at the same time. You provide the range to sum first, then one or more criteria range/criteria pairs. Excel checks each row and includes the value only when all conditions match.

Use SUMIFS when SUMIF isn't enough because you need more than one filter. Common scenarios include totaling sales for a specific rep in a specific region, summing expenses for a category within a date range, or adding up orders above a dollar threshold from a particular customer.

For counting instead of summing, use COUNTIFS. For averages across multiple conditions, try AVERAGEIFS. SUMIFS uses AND logic, meaning every condition must be true for a row to be included. If you need OR logic, add two SUMIFS formulas together or use SUMPRODUCT.

Examples

Wildcards in text criteria

See how the wildcard "Soft*" matches both "Software" and "Software Service" in this product table. Change a category name and watch the totals in rows 8 and 9 update instantly.

Spreadsheet editor

OR logic with SUMIFS

Watch what happens in row 9: asking for rows that are both "Marketing" AND "Sales" at once always returns 0. Row 8 shows the correct fix: add two SUMIFS results together for true OR logic.

Spreadsheet editor

Two conditions on the same column

Notice how two criteria on the same month column create a numeric window. Only rows where month is between 1 and 3 qualify. Try changing the bounds or swapping "Travel" for another category.

Spreadsheet editor

Watch out for

⚠

Sum range position is different from SUMIF

In SUMIF, the sum range is the third argument. In SUMIFS, it comes first. Swapping the order causes wrong results or errors.

→ Always start SUMIFS with the range you want to sum, then list your criteria range/criteria pairs after it.

⚠

Criteria ranges must match the sum range in size

If your sum range is C2:C100 but a criteria range is A2:A50, Excel returns a #VALUE! error because the ranges don't have the same number of rows.

→ Make every criteria range the same shape and size as the sum range. If sum_range is 99 rows tall, each criteria range must also be 99 rows tall.

⚠

SUMIFS only uses AND logic

SUMIFS totals a row only when every condition is true at the same time. If you need rows where condition A or condition B is true, SUMIFS alone won't work.

→ For OR logic, add two SUMIFS results together: =SUMIFS(...condition A...) + SUMIFS(...condition B...). Use SUMPRODUCT for more complex OR scenarios.

⚠

Text criteria missing quotation marks

Writing =SUMIFS(C2:C10, A2:A10, North) without quotes tells Excel to look for a named range called North, not the text "North". This usually returns 0.

→ Wrap text criteria in double quotes: "North". To reference a cell, use a cell address directly without quotes: A1.

Tips & notes

Text criteria in SUMIFS are case-insensitive, so "north" and "NORTH" match the same cells. Wildcards work in text criteria: ? matches any single character and * matches any sequence of characters. If no rows meet all conditions, SUMIFS returns 0, not an error. SUMIFS supports up to 127 criteria range/criteria pairs.

Common questions

Can I use SUMIFS with a date range?

Yes. Use comparison operators with dates as criteria. For example, ">="&DATE(2024,1,1) as a criteria string matches dates on or after January 1, 2024. You can also reference a cell containing a date directly.

Why does SUMIFS return 0 when I expect a number?

Common causes include a typo or extra space in a text criterion, the sum range and criteria ranges being different sizes, or missing quote marks around a text value in the formula. Check each condition one at a time to find the mismatch.

What is the difference between SUMIF and SUMIFS?

SUMIF handles one condition and puts the sum range as the third argument. SUMIFS handles multiple conditions and puts the sum range first. If you only need one condition, both work, but SUMIFS is worth learning since it scales as your needs grow.

Can I use wildcards in SUMIFS criteria?

Yes. Use * to match any sequence of characters and ? to match a single character. For example, "North*" matches "North", "Northeast", and "Northwest". Wildcards only work with text criteria, not numbers.