SUMIFS function

This article describes the formula syntax and usage of the SUMIFS function in Microsoft Excel.

Description

Adds the cells in a range that meet multiple criteria. For example, if you want to sum the numbers in the range A1:A20 only if the corresponding numbers in B1:B20 are greater than zero (0) and the corresponding numbers in C1:C20 are less than 10, you can use the following formula:

=SUMIFS(A1:A20, B1:B20, ">0", C1:C20, "<10")

Important   The order of arguments differ between the SUMIFS and SUMIF functions. In particular, the sum_range argument is the first argument in SUMIFS, but it is the third argument in SUMIF. If you are copying and editing these similar functions, make sure you put the arguments in the correct order.

Syntax

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

The SUMIFS function syntax has the following arguments:

  • sum_range    Required. One or more cells to sum, including numbers or names, ranges, or cell references that contain numbers. Blank and text values are ignored.

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

  • criteria1    Required. 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. For example, criteria can be expressed as 32, ">32", B4, "apples", or "32."

  • criteria_range2, criteria2, …    Optional. Additional ranges and their associated criteria. Up to 127 range/criteria pairs are allowed.

Remarks

  • 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 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 (~) before the character.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

Quantity Sold

Product

Salesperson

5

Apples

Tom

4

Apples

Sarah

15

Artichokes

Tom

3

Artichokes

Sarah

22

Bananas

Tom

12

Bananas

Sarah

10

Carrots

Tom

33

Carrots

Sarah

Formula

Description

=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, "Tom")

Adds the number of products that begin with A and were sold by Tom. It uses the wildcard character * in Criteria1, "=A*" to look for matching product names in Criterial_range1 B2:B9, and looks for the name "Tom" in Criterial_range2 C2:C9. It then adds the numbers in Sum_range A2:A9 that meet both conditions. The result is 20.

=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, "Tom")

Adds the number of products that aren’t bananas and are sold by Tom. It excludes bananas by using <> in the Criteria1, "<>Bananas", and looks for the name "Tom" in Criterial_range2 C2:C9. It then adds the numbers in Sum_range A2:A9 that meet both conditions. The result is 30.

Applies To: Excel 2007, Excel 2010, Excel Starter



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language