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.
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])
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.
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 (~?) (~*).
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.
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.
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.
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