The Excel SUMIF Function

Related Functions:

Function Description

The Excel Sumif function finds the values in a supplied array, that satisfy a given criteria, and returns the sum of the corresponding values in a second supplied array.

The syntax of the function is:

SUMIF( range, criteria, [sum_range] )

where the function arguments are:

range	-	An array of values (or range of cells containing values) to be tested against the supplied criteria.
criteria	-	The condition to be tested against each of the values in the supplied range.
[sum_range]	-	An optional array of numeric values (or cells containing numeric values), which are to be added together, if the corresponding range entry satisfies the supplied criteria. If the [sum_range] argument is omitted, the values from the range argument are summed instead.

Wildcards

The following wildcards can be used in text-related criteria:

? - matches any single character
* - matches any sequence of characters

(if you do actually want to find the ? or * character, type the ~ symbol before this character in your search.)

E.g. the condition "A*e" will match all cells containing a text string beginning with "A" and ending in "e".

The supplied criteria argument can be either:

a numeric value (which may be an integer, decimal, date, time, or logical value) (e.g. 10, 01/01/2008, TRUE)

a text string (e.g. "Text", "Thursday")

an expression (e.g. ">12", "<>0").

Note that:

If the criteria is a text string or an expression, this must be supplied to the Sumif function in quotes;
The Excel Sumif function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be evaluated as equal.

Excel Sumif Function Examples

Example 1

The following spreadsheet shows three examples of the Excel Sumif function used with text based critia.

For each call to the Excel Sumif function, the range argument (to be tested against the criteria) is either the cell range A2-A9 or the cell range B2-B9, and the [sum_range] argument (containing the values to be summed) is the cell range C2-C9.

Formulas:

	A	B	C
1	Month	Team	Sales
2	Jan	North 1	$36,693
3	Jan	North 2	$22,100
4	Jan	South 1	$53,321
5	Jan	South 2	$34,440
6	Feb	North 1	$29,889
7	Feb	North 2	$50,090
8	Feb	South 1	$32,080
9	Feb	South 2	$45,500
10
11	=SUMIF( A2:A9, "Feb", C2:C9 )
12	=SUMIF( B2:B9, "North 1", C2:C9 )
13	*=SUMIF( B2:B9, "North", C2:C9 )**

Results:

	A	B	C
1	Month	Team	Sales
2	Jan	North 1	$36,693
3	Jan	North 2	$22,100
4	Jan	South 1	$53,321
5	Jan	South 2	$34,440
6	Feb	North 1	$29,889
7	Feb	North 2	$50,090
8	Feb	South 1	$32,080
9	Feb	South 2	$45,500
10
11	$157,559	- sum of cells C6-C9
12	$66,582	- sum of cells C2 & C6
13	$138,772	- sum of cells C2, C3, C6 & C7

Note that, in the example above:

The function in cell A13 uses the wildcard * and so finds cells in the range B2-B9 that begin with the text string "North". This is satisfied by the values "North 1" and "North 2".
In all three examples, the text based criteria (including the wildcard) are encased in quotes.

Example 2

The following example shows the Excel Sumif function using critia based on numeric values.

Formulas:

	A	B
1	1	200
2	2	45
3	1	550
4	2	450
5	1	20
6
7	=SUMIF( A1:A5, 1, B1:B5 )
8	=SUMIF(B1:B5, ">100" )

Results:

	A	B
1	1	200
2	2	45
3	1	550
4	2	450
5	1	20
6
7	770	- sum of cells B1, B3 & B5
8	1200	- sum of cells B1, B3 & B4

Note that, in the above spreadsheet:

The criteria in cell A8 is an expression, and so, is enclosed in quotes.
The [sum_range] argument is omitted from the function in cell A8 and so the values in the range array (i.e. cells B2-B6) are summed.

For further examples of the Excel Sumif function, see the Microsoft Office website.

Sumif Function Error

If you get an error from the Excel Sumif function, this is likely to be the #VALUE! error:

Common Error

#VALUE!

Occurs if the supplied criteria is a text string that is more than 255 characters long.

Return to the Excel Math Functions Page
Return to the List of All Built-In Excel Functions