Excel SUMIFS Function

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.
Examples
EXAMPLE 1
The example may be easier to understand if you copy it to a blank worksheet.

How do I copy an example?
1. Select the example in this article.
IMPORTANT Do not select the row or column headers.
Selecting an example from Help
2.
3.
4.
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.
5.
Quantity Sold
Product
Salesperson
Apples
Apples
15
Artichokes
Artichokes
22
Bananas
12
Bananas
10
Carrots
33
Carrots
10
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
11
12
EXAMPLE 2: ADDING AMOUNTS FROM BANK ACCOUNTS BASED ON INTEREST PAID

2.
3.
4.
Press CTRL+C.
5.
button.
A
Totals
Account 1
Account 2
Account 3
Account 4
Amount in dollars
100
390
8321
500
Interest paid (2000)
1%
0.5%
3%
4%
1%
1.3%
2.1%
2%
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
1
2
3
EXAMPLE 3: ADDING RAINFALL FOR SPECIFIC DAYS

2.
3.
4.
5.
Press CTRL+C.
button.
A
Daily
First Day
Second
Third
Fourth
1
2
4
5
Measurements
Day
Day
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.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 12-hour periods for each day.
2.
3.
4.
5.
Press CTRL+C.
button.
A
First Day
Morning and
Evening
Measurements
Second
Day
Third
Day
Fourth
Day
AM: rain (total

inches)
1.3
1.5
PM: rain (total

inches)
0.8
2.5
56
44
40
38
AM: average
temperature
(degrees)
54
34
38
77
PM: average
temperature
(degrees)
13
AM: average wind

speed (miles per
hour)
33
12
PM: average wind

speed (miles per
hour)
Formula
Description
Result
=SUMIFS(B2:E3,
B4:E5, ">=40",
B6:E7, "<10")
Adds the total amount of rainfall for 12-hour

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: ENTERING CRITERIA AS A REFERENCE OR BY USING WILDCARD CHARACTERS

2.
3.
4.
Press CTRL+C.
button.
5.
Homes
Squ
are
Feet
Bedro
oms
Bat
hs
Gara
ge
Ye
ar
bu
ilt
Price
House1
1200
yes
19
40
$125,
000
House2
1580
1.5
no
19
65
$217,
000
House3
2200
yes
20
03
$376,
000
House4
1750
2.5
yes
20
01
$249,
000
House5
2140
yes
19
98
$199,
000
=">"&FIXED(SUM(1,1),0)
="ye
s"
="y*"
Formula
Description
Resu
lt
=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.
6250
00
=AVERAGEIFS(G2:G6,C2:C6,">2",E2:E6,C7,F2:F6,">19
99",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.
3125
00
2
3
4
5
6
7
8
1
0
See Also
Math and trigonometry functions (reference)

Excel SUMIFS Function

Uploaded by

Copyright:

Available Formats

Excel SUMIFS Function

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Excel SUMIFS Function

Uploaded by

Copyright:

Available Formats

SUMIFS function

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

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

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

Selecting an example from Help

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

Adds the total number of products sold that

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

Adds the total number of products (not

EXAMPLE 2: ADDING AMOUNTS FROM BANK ACCOUNTS BASED ON INTEREST PAID

Selecting an example from Help

Interest paid (2000)

Interest paid (2001)

Interest paid (2002)

Total amounts from each bank

Total amounts from each bank

EXAMPLE 3: ADDING RAINFALL FOR SPECIFIC DAYS

Selecting an example from Help

Rain (total inches)

Adds the total amount of rainfall for days when the

Selecting an example from Help

AM: rain (total

PM: rain (total

AM: average wind

PM: average wind

Adds the total amount of rainfall for 12-hour

EXAMPLE 5: ENTERING CRITERIA AS A REFERENCE OR BY USING WILDCARD CHARACTERS

Selecting an example from Help

Adds the price of those

Returns the average price

Math and trigonometry functions (reference)

You might also like