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 :
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 range|
An optional array of numeric values (or cells containing numbers) which are to be added together, if the corresponding range entry satisfies the supplied criteriaIf the sum_range argument is omitted, the values from the range argument are used instead
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:
|-||a numeric value (which may be an integer, decimal, date, time, or logical value) (eg. 10, 01/01/2008, TRUE)|
|-||a text string (eg. "Text", "Friday")|
|-||an expression (eg. ">12", "<>0")|
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.
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".
The following example shows the Excel Sumif function using critia based on numerical values.
Further examples of the Excel Sumif function can be found on the Microsoft Office website.
The error that you are most likely to get from the Excel Sumif function is the #VALUE! error :
|#VALUE!||-||Occurs if the supplied criteria is a text string that is more than 255 characters long|