SUMIF Function

The SUMIF function adds a conditional component to the SUM function, enabling you to sum up data in a range based on certain criteria.

Syntax

SUMIF(range, criteria, [sum_range])

Arguments

Argument	Description
range	Required. This is the range of cells that you want to evaluate based on the condition in criteria .
criteria	Required. This is the condition (or logical test) that is used to determine which cells are summed up in range . This can be an expression, cell reference, text, or function.
sum_range	Optional. You use this argument if you want your return value (that is the values being summed up) to be different from those used to filter the data. If this argument is omitted, then the cells specified in range are used.

A couple of things to take into consideration when using advanced IF functions:

• If the criteria argument is text or includes logical or mathematical symbols like greater than (>), for example, the whole criteria argument must be enclosed in double quotes (“”). Quotation marks are not required if criteria is a numeric value.
• Cells in the range argument must be numbers, names (for example, named ranges or tables), arrays, or references that contain numbers. Text values and blanks are ignored.
• You can use wildcard characters (like a question mark “?” or an asterisk “*”) as the criteria argument. A question mark matches any single character while an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk in your data then type a tilde (~) before the character, for example, “~?”.

Example 1

In this example, we’re using SUMIF to only sum up values in Sales that are over $5,000.

The formula used is:

=SUMIF(A2:A11,">5000")

The formula is using the criteria argument of “>5000” to filter which values will be added to the sum from the range A2:A11.

Example 2

In this example, we’re using SUMIF to sum up all Commissions for sales over $5,000. We’ll be using the sum_range argument to specify the cells we want to sum up as they are different from the cells specified in the range argument.

The formula we use is:

=SUMIF(A2:A11,">5000", B2:B11)

Formula explanation

=SUMIF(A2:A11,">5000", B2:B11)

The formula is using the criteria argument “>5000” to select the values in column A (Sales) for which the corresponding values in column B (Commission) will be added to the sum. So, even though we applied the criteria to column A, the values summed up come from Column B.