How to Use the SUMIF Function and Formula in Excel
In this tutorial, you'll learn how to use the SUMIF function and formula in Excel. SUMIF function in Excel is a function to sums the values in a range of cells that meet single criteria.
The syntax of the SUMIF function is as follows:
=SUMIF(range, criteria, [sum_range])
- Range: The range of cells that you want to be evaluated by the criteria you specify.
- Criteria: The criteria used to determine which cells to add.
- Sum_range: [Optional]The actual cells in a range that you want to sum.
- If sum_range omitted, the Excel SUMIF function adds the cells specified in the range argument.
- Text criteria or criteria, which include mathematical or logical symbols, need to be enclosed in double quotation marks. If the specified criteria are numeric, double quotation marks are not required.
- You can use the wildcard characters* and ? in criteria.
- SUMIF is not case-sensitive. So, for example, JOHN and John are treated the same.
SUMIF Function Examples
The following table is a sample list of sales records we're going to use in our examples.
|1||Date||Code||Item Name||Quantity||Price ($)||Amount ($)||Employee|
|2||10/20/2019||Item1001||Sony Desktop PC 1||2||1050||2100||Roland|
|3||10/20/2019||Item1002||Sony Desktop PC 2||2||950||1900||James|
|4||10/20/2019||Item1001||Sony Desktop PC 1||1||1250||1250||James|
|5||10/20/2019||Item1003||Sony Desktop PC 3||2||450||900||Roland|
|6||10/21/2019||Item1002||Sony Desktop PC 2||3||1000||3000||Roland|
|7||10/22/2019||Item1001||Sony Desktop PC 1||1||1190||1190||Roland|
|8||10/23/2019||Item1003||Sony Desktop PC 3||2||450||900||James|
SUMIF with DATE Function
Let's say you want to sum all the values in the range F2:F8 where the date is 10/20/2019, then you can write the formula as follows:
=SUMIF(A2:A8, DATE(2019,10,20), F2:F8)
Here, A2:A8 is the criteria range, DATE(2016,10,5) is criteria, and F2:F8 is the range of cells you want to sum.
SUMIF sums all the values in the range F2: F8, where the value of the cells in the Date column is equal to October 20th, 2019
The above formula returns 6150.
Note that here we use the DATE function as criteria.
If you use the text criteria, the SUMIF formula will look like this:
=SUMIF(A2:A8, "10/20/2019", F2:F8)
This formula returns the same result (6150) as the previous formula.
SUMIF with a Reference Criteria
In this example, you'll learn how to use the reference criteria in the SUMIF function.
The formula using a reference in criteria would look like this:
=SUMIF(A2:A8, H2, F2:F8)
H2 is a cell reference to criteria.
SUMIF with Logical Operators in Criteria
In this example, I show you how to use logical operators, ">=" , "<=", "<" or ">" in a text criteria.
In this example, let's say you want to sum only the values of cells that are greater than 2000 in the range of cells.
You can write the SUMIF formula as follows:
Here, as you can see, there are only two arguments and the third (sum_range) argument omitted. So, the range F2: F8 becomes both range and sum_range. Based on the specified condition above, SUMIF sums all the values that are greater than 2000 in the range F2: F8, and returns 5100.
Let's look at another example:
As you can see, the second argument is a text criterion that includes the logical operator (>). SUMIF sums all the values of cells that are greater than 10/22/2019 in column A and returns 900.
Next, let's look at an example that uses a logical operator (>=):
SUMIF sums all the values of cells that are greater than or equal to 10/22/2019 in column A and returns 2090.
Similarly, to use a logical operator "<=" in criteria, you can write the SUMIF formula as follows:
SUMIF sums all the values of cells that are less than or equal to 10/202019 in column A and returns 6150.
You also can perform a partial matching in the SUMIF function. To perform a partial matching, you need to use the wildcard characters (*) and (?) in text criteria.
Partial Matching Using Asterisk (*)
In this example, we use (*) in a text criterion.
Let's take a look at a formula below:
The criteria So* matches any text string that begins with So, and for the rest, everything would be fine. So, SUMIF sums all the values of cells that the item name starts with So.
The result of the above formula is 9990.
Note that, the wildcard characters (*) matches any sequence of characters.
Partial Matching Using Question Mark (?)
In this example, we use the wildcard (?) in a text criterion.
Let's take a look at the formula below.
=SUMIF(C2:C8, "???? Desktop PC", F2:F8)
Note that a wildcard (?) is different from (*). It matches any single character.
The criteria ???? Desktop PC matches any text string that begins with any four lengths text string and ends with Desktop PC.
SUMIF sums all the values of cells that the item name matches the specified condition above and returns 0.
SUMIF is not Case-Sensitive
SUMIF is not case-sensitive, so James, james, and JAMES text criteria are treated the same.
Let's take a look at the formula below.
In the above formula, you can replace the criteria James with JAMES, james or JaMeS, because SUMIF treats the lowercase and uppercase characters the same.
If you want to sum all the values in a range of cells that meet more than one criteria, use SUMIFS.
In this tutorial, you've learned how to use the Excel SUMIF function. SUMIF is used to sum all the values in a range of cells that meet single criteria. If you want to specify multiple criteria, use the SUMIFS function.