This article describes the formula syntax and usage of the SUMIFS function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Office Excel.
Description
Adds the cells in a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) 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 (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):
 sum_range Required. One or more cells to sum, including numbers or names, ranges, or cell references (cell reference: The set of coordinates that a cell occupies on a worksheet. For example, the reference of the cell that appears at the intersection of column B and row 3 is B3.) 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 1
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
After you copy the example to a blank worksheet, you can adapt it to suit your needs.

1 
2 
3 
4 
5 
6 
7 
8 
9 
10 
11 
12 

A 
B 
C 
Quantity Sold 
Product 
Salesperson 
5 
Apples 
1 
4 
Apples 
2 
15 
Artichokes 
1 
3 
Artichokes 
2 
22 
Bananas 
1 
12 
Bananas 
2 
10 
Carrots 
1 
33 
Carrots 
2 
Formula 
Description 
Result 
=SUMIFS(A2:A9, B2:B9, "=A*", C2:C9, 1) 
Adds the total number of products sold that begin with "A" and that were sold by Salesperson 1. 
20 
=SUMIFS(A2:A9, B2:B9, "<>Bananas", C2:C9, 1) 
Adds the total number of products (not including Bananas) sold by Salesperson 1. 
30 

Example 2
Adding amounts from bank accounts based on interest paid
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
After you copy the example to a blank worksheet, you can adapt it to suit your needs.

A 
B 
C 
D 
E 
Totals 
Account 1 
Account 2 
Account 3 
Account 4 
Amount in dollars 
100 
390 
8321 
500 
Interest paid (2000) 
1% 
0.5% 
3% 
4% 
Interest paid (2001) 
1% 
1.3% 
2.1% 
2% 
Interest paid (2002) 
0.5% 
3% 
1% 
4% 
Formula 
Description 
Result 
=SUMIFS(B2:E2, B3:E3, ">3%", B4:E4, ">=2%") 
Total amounts from each bank account where the interest was greater than 3% for the year 2000 and greater than or equal to 2% for the year 2001. 
500 
=SUMIFS(B2:E2, B5:E5, ">=1%", B5:E5, "<=3%", B4:E4, ">1%") 
Total amounts from each bank account where the interest was between 1% and 3% for the year 2002 and greater than 1% for the year 2001. 
8711 

Example 3
Adding rainfall for specific days
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
After you copy the example to a blank worksheet, you can adapt it to suit your needs.

A 
B 
C 
D 
E 
Daily Measurements 
First Day 
Second Day 
Third Day 
Fourth Day 
Rain (total inches) 
3.3 
0.8 
5.5 
5.5 
Average temperature (degrees) 
55 
39 
39 
57.5 
Average wind speed (miles per hour) 
6.5 
19.5 
6 
6.5 
Formula 
Description 
Result 
=SUMIFS(B2:E2, B3:E3, ">=40", B4:E4, "<10") 
Adds the total amount of rainfall for days when the average temperature was at least 40 degrees Fahrenheit and the average wind speed was less than 10 miles per hour.
Only cells B2 and E2 are summed because, for each column (B through E), the values in both rows 3 and 4 must meet criteria1 and criteria2, respectively. Cells B3 and B4 meet both criteria, as do E3 and E4. However, neither C3 nor C4 meet either criteria. Finally, although D4 meets criteria2, D3 fails to meet criteria1.

8.8 

Example 4
Adding rainfall for morning and evening periods of specific days
This example expands on the data in Example 3, separating the rainfall, average temperatures, and average wind speed into two 12hour periods for each day.
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
After you copy the example to a blank worksheet, you can adapt it to suit your needs.

A 
B 
C 
D 
E 
Morning and Evening Measurements 
First Day 
Second Day 
Third Day 
Fourth Day 
AM: rain (total inches) 
1.3 
0 
1.5 
3 
PM: rain (total inches) 
2 
0.8 
4 
2.5 
AM: average temperature (degrees) 
56 
44 
40 
38 
PM: average temperature (degrees) 
54 
34 
38 
77 
AM: average wind speed (miles per hour) 
13 
6 
8 
1 
PM: average wind speed (miles per hour) 
0 
33 
4 
12 
Formula 
Description 
Result 
=SUMIFS(B2:E3, B4:E5, ">=40", B6:E7, "<10") 
Adds the total amount of rainfall for 12hour periods when the average temperature was at least 40 degrees Fahrenheit and the average wind speed was less than 10 miles per hour.
Only cells B3, C2, and D2 are summed, because their corresponding cells meet both criteria. The corresponding cells for B3 are B5 and B7, the corresponding cells for C2 are C4 and C6, and the corresponding cells for D2 are D4 and D6.
As an example of data excluded from the operation, the corresponding cells of B2 (B4 and B6) do not meet both criteria; specifically, cell B6 fails because its value (13) is greater than criteria2 (10).

3.5 

Example 5
Criteria entered as reference and by using wildcard characters
The example may be easier to understand if you copy it to a blank worksheet.
How do I copy an example?
 Select the example in this article.
Important Do not select the row or column headers.
Selecting an example from Help
 Press CTRL+C.
 In Excel, create a blank workbook or worksheet.
 In the worksheet, select cell A1, and press CTRL+V.
Important For the example to work properly, you must paste it into cell A1 of the worksheet.
 To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.
After you copy the example to a blank worksheet, you can adapt it to suit your needs.

A 
B 
C 
D 
E 
F 
G 
Homes 
Square Feet 
Bedrooms 
Baths 
Garage 
Year built 
Price 
House1 
1200 
2 
1 
yes 
1940 
$125,000 
House2 
1580 
3 
1.5 
no 
1965 
$217,000 
House3 
2200 
4 
3 
yes 
2003 
$376,000 
House4 
1750 
3 
2.5 
yes 
2001 
$249,000 
House5 
2140 
4 
3 
yes 
1998 
$199,000 
=">"&FIXED(SUM(1,1),0) 
="yes" 
="y*" 




Formula 
Description 
Result 


=SUMIFS(G2:G6,C2:C6,A7,E2:E6,B7,F2:F6,">1999",F2:F6,"<2004") 
Adds the price of those homes that have at least 3 bedrooms, a garage, and are between 5 and 10 years old as of 2009. 
625000 


=AVERAGEIFS(G2:G6,C2:C6,">2",E2:E6,C7,F2:F6,">1999",F2:F6,"<2004") 
Returns the average price for homes that have at least 3 bedrooms, a garage, and are between 5 and 10 years old as of 2009. 
312500 


