Using the Excel SUMIFS Function


The Excel SUMIFS function expands the capabilities of the SUMIF function and is used to add all arguments that meet a user specified criteria. While SUMIF only accepts a single criteria, SUMIFS can handle multiple criteria. For example, SUMIFS could be used to sum the number of sales people in your company who (1) have sales exceeding a specified threshold and (2) who live in a specific region. Criteria can be based on dates, numbers and text. The function supports the use of logical operators (<, >, =, <>) and wildcards (*, ?).


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

Syntax Breakdown

The sum_range argument is required and is the range of cells that you would like to evaluate by the criteria argument. Cells that are blank or contain text values will be ignored. Dates in standard Excel format are accepted.

Criteria_range1 is also required and is the range that will be tested by criteria1.

The criteria1 argument is required. This argument defines which cells in criteria_range1 will be added.

Criteria1 can be entered in the form of a number, expression, cell reference, or text. For example, 5, "<5", A1, or "5" are all acceptable entries for criteria.

If you use text in your criteria or logical symbols it is important to enclose them in double quotation marks ("). Double quotation marks are not required for numeric criteria.

Criteria_range2, criteria2, ...
Criteria_range2, criteria2 and any subsequent arguments are optional. The SUMIFS function can accept up to 127 range/criteria pairs.

Usage Notes

The SUMIFS function allows users to sum a range that meets multiple, specific criteria. SUMIFS is useful when you need to apply more than one set of criteria to more than one range.

Using Wildcard Characters
Question marks (?) and asterisks (*), known as wildcard characters, are allowed in the criteria argument. The question mark will match a single character and the asterisk will match any sequence of characters.

Enter the tilde (~) character before the question mark or asterisk (i.e. ~* or ~?) if you are searching for an actual question mark or asterisk.

Excel SUMIFS Wildcard Example

SUMIF and SUMIFS have a different order for the arguments. In SUMIFS, the sum_range argument is first. However, this is the third argument in SUMIF.

If you are copying or editing these functions it is critical to ensure the arguments are in the correct order or you may have unreliable results.

Zero Error
If the function returns zero (0) as the result when this is not expected you may need to check that text values in Criteria1, Criteria2, etc. are enclosed in quotation marks. This is a common error.