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:
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]||-||[sum_range] argument is omitted, the values from the range argument are used instead.If the|
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.)eg. 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:
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 spreadsheet below shows three examples of the Excel Sumif function used with text based critia.
Note that, in the example above:
The following example shows the Excel Sumif function using critia based on numeric values.
Note that, in the above spreadsheet:
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.|