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 :
|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.
The supplied criteria argument 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 Sumif 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.
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|