How to Use the Excel SUMIF Function
In this tutorial, you'll learn how to use the Excel SUMIF function to sum the numerical values in a range of cells that meet a single condition.
While the SUM function sums up numerical values, the SUMIF function sums up only those that meet a specified condition.
For example, you may want to know the sales of only the products that belong to the fruit category.
If you can master various functions, including the SUMIF function, you can improve your Excel work efficiency.
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 cells to add.
- Sum_range: [Optional]. The actual cells in a range that you want to sum.
- If sum_range omitted, a SUMIF 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 specified criteria are numeric, double quotation marks are not required.
- You can use the wildcard characters* and ? in criteria.
- SUMIF is not case-sensitive. For example, JOHN and John are the same.
SUMIF Function Examples
Suppose we have the demo data, as shown below.
|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 and DATE Function
Let's say you want to sum all of the values in the range F2: F8, where the date is 10/20/2019.
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 the criteria, and F2: F8 is the range of cells to sum.
SUMIF sums all of the values in the range F2: F8, where the Date column is equal to October 20th, 2019.
The formula returns 6150.
Note that here the DATE function is used as criteria.
If you use text criteria, the SUMIF formula will look like this:
=SUMIF(A2:A8, "10/20/2019", F2:F8)
The formula returns the same value (6150).
SUMIF with a Reference Criteria
The following example shows how to use a reference in the criteria:
=SUMIF(A2:A8, H2, F2:F8)
H2 is a cell reference to criteria.
SUMIF with Logical Operators in Criteria
You can also use logical operators, ">=" , "<=", "<" or ">" in text criteria.
Let's say you want to sum only the values of cells that are greater than 2000 in the range of cells.
The SUMIF formula is as follows:
Here, as you can see, there are only two arguments and the third (sum_range) argument omitted. The range F2: F8 becomes both range and sum_range. Based on the above condition, SUMIF returns 5100.
You can also use a logical operator, as the following example shows:
As you can see, criteria is a text 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.
You can use a logical operation greater than or equal to (>=), as the following example shows:
Similarly, you can also use a logical operator less than or equal to (<=) in criteria, as the following example shows:
You also can perform a partial matching in the SUMIF function. To perform a partial matching, you need to use the wildcard characters (*) or (?), or both in criteria.
Partial Matching Using Asterisk (*)
The following illustrates an example of using (*) in criteria:
The criteria So* matches any text string that begins with So, and everything is ok for the rest.
SUMIF sums all the values of cells that the item name starts with So.
The formula returns 9990.
Note that, the wildcard characters (*) matches any sequence of characters.
Partial Matching Using Question Mark (?)
You can use the wildcard (?) in criteria, as the following example shows:
=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 returns 0 because no match meets the specified condition.
SUMIF is not case-sensitive
SUMIF is not case-sensitive, so James, james, and JAMES text criteria are treated the same.
Here is an example:
You can also replace the criteria James with JAMES, James, or James, because SUMIF treats the lowercase and uppercase characters the same.
In this tutorial, you've learned how to use the Excel SUMIF function.
With the SUMIF function, you can sum all the values in a range of cells that meet single criteria. If you want to specify multiple criteria, use the SUMIFS function.
The SUMIF and SUMIFS functions are both easy to use and convenient. When you have a huge amount of data, they can help you improve your work efficiency. Once you understand how to use it, you can easily find the sum of the numbers that fit the conditions without complicated operations or editing.