The SUMIFS function in Excel allows you to sum the values in a range of cells that meet multiple criteria. For example, you might use the SUMIFS function in a sales spreadsheet to to add up the value of sales of a specified product by a given sales person (e.g. the value of all sales of a microwave oven made by John). This lesson shows you how to use SUMIFS in your own spreadsheets.

Note that SUMIFS was introduced in Excel 2007. This lesson therefore only applies to Excel 2007, 2010, 2013 and 2016


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

The SUMIFS functions syntax has the following arguments.

sum_range: One or more cells to sum, including numbers or names, range, or cell reference that contain numbers. Blank and text value are ignored.

criteria_range1: The first range in which to evaluate the associated criteria.

criteria1: The criteria in the form of a number, expression, cell reference, or text that define which cells in the criteria_range1 argument will be added.

criteria_range2, criteria 2: Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.


Each cell in the sum_range argument is summed only if all of the corresponding criteria specified are true for that cell. For example, suppose that a formula contains two criteria_range arguments. If the first cell of criteria_range1 meets criteria1, and and the first cell of criteria_range2 meets critera2, 
the first cell of sum_range is added to the sum, and so on, for the remaining cells in the specified ranges.

Cells in the sum_range argument that contain TRUE evaluate to 1; cells in sum_range that contain FALSE evaluate to 0 (zero).

Unlike the range and criteria arguments in the SUMIF function, in the SUMIFS function, each criteria_range argument must contain the same number of rows and columns as the sum_range argument.

You can use the wildcard characters — the question mark (?) and asterisk (*) — in criteria. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde


Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2016 for Mac, Excel for Mac 2011, Excel Online, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel Starter, Excel for Android phones