SUMIF Function

Excel SUMIF Function

The SUMIF function in Excel is used to sum all of the values within the user-specified range, if the value meets the user-defined criterion. The defined criterion can be evaluated against dates, numbers, and text strings.

	A	B	C	D
1	Address	City	Value (thousands)	Units
2	123 Balboa Ave	San Diego	400	1
3	999 Main St	San Francisco	600	2
4	456 Ave A	Los Angeles	450	1
5	555 La Jolla Blvd	San Diego	650	4
6	999 Ave E	Los Angeles	350	3
7	111 Ave A	Los Angeles	400	2

Syntax of the SUMIF Function

The SUMIF function has two required arguments (values separated by commas) and one optional argument, and is written as follows:

=SUMIF(range, criteria, [sum_range])

Range (required) \- The range argument is the range of cells that are to be evaluated by the criterion. Each cell within this range may contain a number, date, or text string.
Criteria (required) \- The criteria is a number, expression, reference, text, or function to define whether the defined range is to be summed. The criteria argument is capable of parsing logical operators (>, <, <>, =) and wildcards (?, \*).
Sum range (optional) \- The sum_range is the range of cells to be summed. If the sum_range argument is omitted, the range argument will be summed instead. This argument is OPTIONAL and is only used when the range to be summed is different than the range evaluated against the specified criteria.

Note: sum_range should be the same size and shape as range. If sum_range is a larger area than range, the formula will sum a range of cells equal to the size of the range defined in the range argument.

What Does the SUMIF Function Sum in Excel?

The SUMIF function in Excel can use logical operators evaluate matches. Here's a quick showcasing of their purposes:

	Operator	Meaning
1	<	Less than
2	>	Greater than
3	<>	Greater than or less than (not equal)
4	<=	Less than or equal
5	>=	Greater than or equal

The SUMIF function also uses Excel's wildcard operators evaluate matches. Here's a quick showcasing of their purposes:

	Wildcard	Meaning
1	?	Single character wildcard
2	\*	Wildcard string of undefined length

As seen above, the wildcard operators in Excel are a question mark (?) and an asterisk (\). If you need to use a literal question mark or asterisk in their function, place a tilde (~) before the symbol (~?) (~\).

Examples Using the SUMIF Function

Take the following example of an Excel table with employee sales.

	A	B	C
1	Employee	Sale ID #	Sales Total ($)
2	Simon	1	86
3	Martha	2	77
4	George	3	53
5	Mark	4	40
6	Isabella	5	80
7	Mark	6	49

Say you wanted to evaluate the total of all sales over $50. You could use the formula below:

=SUMIF(C2:C7, ">50")

The formula would return a value of $296. Notice how the comparison operator goes inside quotes.

What if you wanted to find the sum of all of Mark's sales? You could use the following formula which makes use of the SUMIF function's optional sum_range argument:

=SUMIF(A2:A7, "Mark", C2:C7)

We're evaluating the cells in the first range against the criteria, and summing the corresponding values in the sum range. So this formula would find the sum of all sales where employee = "Mark", so we should end up with a value of 89.

Using Cell References in the SUMIF Function

The SUMIF function can compare cells in the range against other cells in your Excel sheet. To use a logical operator or wildcard with a cell reference, you must enclose the operator in quotes and concatenate the comparison cell with the & symbol.

	A	B	C	D	E	F
1	Department	Item	Price ($)		Comparison Price ($):	1.05
2	Produce	Onion	0.03
3	Produce	Apple	0.02
4	Produce	Pear	0.05
5	Deli	Chicken Breast	0.22
6	Deli	Large Pizza	2.05
7	Deli	Ham Sandwich	1.52

If one wanted to find the sum of prices that are less than the comparison price in cell F1, one would use the following formula:

=SUMIF(C2:C7, "<"&F1)

The formula would return a value of 0.32 because that's the sum of all prices that are less than 1.05.

Using Dates in the SUMIF Function

The SUMIF function is able to use valid Excel formatted dates for the criteria argument.

	A	B	C	D	E	F
1	Run #	Date	Distance (miles)		Comparison Date:	22-February-2020
2	1	20-February-2020	6.3
3	2	21-February-2020	4.5
4	3	22-February-2020	7.1
5	4	22-February-2020	7.0
6	5	24-February-2020	5.6
7	6	24-February-2020	7.3

Let's say you wanted to find the total distance you ran for a certain date specified in cell F1.

=SUMIF(B2:B7, F1, C2:C7)

The formula would sum all the miles that you ran on February 22nd and would return 14.1.

Another way to get the same result as the above formula is to use Excel's DATE function. The following example also returns 14.1:

=SUMIF(B2:B7, DATE(2020, 2, 22), C2:C7)

Other than the use of the DATE function, the difference between these two formulas is that the second formula does not use a cell reference. The first function uses a cell as criteria, whereas the second function requires entry into the DATE function. When parsing large amounts of data, especially data that was user-entered, using the DATE function can ensure that all data is in the date format required by Excel.

Using Excel Wildcards with the SUMIF Function

The SUMIF function supports the use of wildcards (\,?) within the criteria argument. The \ wildcard is used to replace any sequence of characters, no matter the length, whereas the ? wildcard is used to replace any single character.

	A	B	C
1	Employee	Sale ID #	Sales Total ($)
2	Andrew Wills	1	86
3	Michael Wills	2	77
4	Jack	3	53
5	Mike	4	40
6	Mark Hering	5	80

Let's say one wanted to find the sale total of all employees with the last name of "Wills" in the table below. One would use the following formula:

=SUMIF(A2:A6, "* Wills", C2:C6)

This formula would return a value of 163.

Using the table above, let's say one wanted to find the total sales of all employees whose names are four characters long. One would use the following formula:

=SUMIF(A2:A6, "????", C2:C6)

The formula would return a value of $93

Continue to SUMIF practice exercises! →