Excel - Microsoft Excel Sumif Function

Description

The Excel Sumif function finds 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: 

Wildcards 

You can also use the following wildcards 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. 

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

The supplied criteria can be either: 

and can be supplied to the function either directly, as a reference to a cell, or as a value returned from another function or formula. 

Note that if your criteria is a text string or an expression, this must be supplied to the function in quotes. 

Also note that the Excel Sumif function is not case-sensitive. So, for example, the text strings "TEXT" and "text" will be considered to be equal. 

Excel Sumif Function Examples

Example 1

The following example shows the Excel Sumif function using critia based on text strings. 

The spreadsheet on the left shows the format of the functions and the spreadsheet on the right shows the results from the function calls. 

In the above spreadsheet, each call to the Excel Sumif function tests the contents of cells A2 to A9 or cells B2 to B9 against the supplied criteria. For of the cells that satisfy the criteria, the corresponding cells in the range C2 to C9 are summed. 

For example, the cells B2 and B6 are both equal to the text string "North 1". Therefore, the Sumif function in cell A12 adds together the corresponding values from cells C2 and C6 ($36,693 and $29,889), and returns the value $66,582. 

Note that the function in cell A13 used the wildcard * and so the criteria is for the cells in the range B2-B9 to begin with the text string "North". This is satisfied by the values "North 1" and "North 2". 

Example 2

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

Sumif Function Errors

The error that you are most likely to get from the Excel Sumif function is the #VALUE! error : 

Common Error