Syntax: = SUMIF( range, criteria, [sum_range] )

The SUMIF formula adds together the cells in a range for the condition or criteria specified. The argument in the formula that is in brackets (i.e., [sum_range] is optional.

The SUMIF function syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

Range: This is a required field. This is the range of cells that will be evaluated by the criteria. The inputs can be numbers, names, a cell reference, or a range. If a range or cell reference is specified, only numbers, or text that Excel can convert into a number will be included—text and empty cells will be ignored.

Note that if any of the arguments or cell values are errors (i.e., #N/A, #VALUE, etc.) the SUM formula will result in an error.

Criteria: This is a required field. The criteria can be in the form of a number, expression, a cell reference, text, or a formula that defines which cells will be added. In other words, the criteria can be expressed as 15, “>3”, “>=5”, C5, “32”, “widgets”, or a formula such as TODAY(). Note that in order to use the greater than and less than inequalities in conjunction with a cell reference; you have to combine the text of the inequality with the cell reference. For example, “>”&B2 (the ampersand is the shortcut to concatenate two references). For the inequalities and text criteria, the double quotation marks are required.

Sum_range: This is an optional field. Sum_range refers to the actual cells to sum if your sum range is different than those specified in the range argument. If the sum_range argument is omitted, Excel sums the cell or range that was specified in the range argument (the same cells to which the criteria are applied).

SUMIF examples:

=SUMIF(A1:A5, “widgets”, “B1:B5)  This formula will add the cells in B1:B5 where the corresponding cell in range A1:A5 is equal to widgets.

=SUMIF(A1:A5, “>10,000”) This formula will add together the cells in the range A1:A5 that are larger than 10,000. Note that the optional argument in the syntax is omitted.

=SUMIF(A1:A5, “<”&Today()) This formula will add together the cells in the range A1:A5 that are less than today. Notice how the less than inequality is in double quotes and the today() formula is added with the use of the ampersand (&).

Leave a Reply