SUMIFS - Microsoft Office Excel

Adds the cells in a range that meet multiple criteria.

Important The order of arguments is different between SUMIFS and SUMIF. 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…)

Sum_range is one or more cells to sum, including numbers or names, arrays, or references that contain numbers. Blank and text values are ignored.

Criteria_range1, criteria_range2, … are 1 to 127 ranges in which to evaluate the associated criteria.

Criteria1, criteria2, … are 1 to 127 criteria in the form of a number, expression, cell reference, or text that define which cells will be added. For example, criteria can be expressed as 32, "32", ">32", "apples", or B4.

Remarks

Each cell in sum_range is summed only if all of the corresponding criteria specified are true for that cell.
Cells in sum_range that contain TRUE evaluate as 1; cells in sum_range that contain FALSE evaluate as 0 (zero).
Unlike the range and criteria arguments in the SUMIF function, in SUMIFS each criteria_range must be the same size and shape as sum_range.
You can use the wildcard characters, 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: 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 to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
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.

1

2
3
4

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: Adding rainfall for specific days

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

Create a blank workbook or worksheet.
Select the example in the Help topic.
Note Do not select the row or column headers.
Selecting an example from Help
Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
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.

1

2
3
4
5
6
7

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")	Add the total amount of rainfall for days when the average temperature was at least 40 degrees Fahrenheit and the average wind speed was less that 10 miles per hour (3.5)

How to copy an example

How to copy an example

See also: