An Overview of SUMIF Function | Microsoft Excel

The SUMIF function is one of the math and trigonometry functions in Microsoft Excel. It can be used as a standalone function as well as in collaboration with the other functions. This article will give you an overview of the SUMIF function in Excel.

Objectives

The SUMIF function calculates the sum of the values based on a single condition. For example, to calculate the sum of all the numbers less than 10, you can use the formula:

=SUMIF(A2:A12,"<10").

Syntax

=SUMIF(range, criteria, [sum_range])

Arguments

• range: Compulsory. It refers to the range of cells where you want to evaluate your criteria.
• criteria: Compulsory. It refers to the condition based on which you want to add up values. Examples: 27, “<27”, D9, “8?”, “orange*”, “*~*”, or NOW().
• sum_range: Non-compulsory. It refers to the cell range that you want to add up. If you don’t want to sum up the cell range specified in the range argument, you need to specify the sum_range. If you specify the range argument as A2:A10 and the sum_range argument as B2:B10, the SUMIF function will sum up the range B2:B10.

Output

A numerical value which is the summation of the range or the sum_range argument.

Example of SUMIF Function

Let’s consider a sales report of an imaginary consumer electronics shop. In the sales report, the Category column has 3 types of product names. Next, the Model column has different product model names corresponding to each product category. Finally, the Sales column has the sales amount in dollars for each of the product models.

Now to calculate the Total Sales amount for the Category, Camera, we can use the following formula using the SUMIF function.

=SUMIF(A2:A10,C12,C2:C10)

Here,

• A2:A10 is the range argument.
• C12 is the criteria argument.
• C2:C10 represents the sum_range argument.

Here, the SUMIF function evaluates the criteria mentioned in cell C12 within the range A2:A10. Based on this condition, the SUMIF function sums up the range C2:C10.

Usage Guide of SUMIF Function

Step_1: Start with inserting an equal sign (=) in a blank cell.

Step_2: Then type SUMIF.

Step_3: Type open parenthesis “(“.

As you type the open parenthesis, Excel shows the syntax of the SUMIF function.

Step_4: Insert a cell range where you want to evaluate your condition.

For this instance, I’m inserting the range A2:A5.

Step_5: Then insert a comma (,).

After inserting the comma, Excel will ask you for the second argument which is criteria.

Step_6: Now insert a criterion based on which you want to sum up values.

Here I’m inserting the cell address C7 which contains the product category, Laptop.

Step_7: Then insert a comma (,) again.

Step_8: Now insert the third argument which is sum_range.

Here I’m inserting the range C2:C5.

Step_9: Finally, add a closing parenthesis “)” to complete the syntax.

Step_10: Now, press ENTER.

The SUMIF function will sum up all the sales amount that matches the product category ‘Laptop’.

Availability

Remarks

• You must use double quotation marks (“”) to specify any text criterion or criteria having mathematical or logical expressions.
• To specify numerical criteria, you don’t need to use double quotation marks.
• To find the matching of any single character using a wildcard, you have to use a question mark (?) such as “8?”. For finding multiple characters, you have to use an asterisk (*) as a wildcard symbol such as “orange*”.
• Sum_range should be equal in size to that of the range argument.
• If you don’t insert the sum_range argument, the SUMIF function will sum up the range argument.
• You need not use quotation marks while using cell addresses in the criteria argument.

Conclusion

The SUMIF function is one of the math and trigonometry functions in Excel. If you have any questions regarding the SUMIF function, please comment below. Thanks!