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.

What is the Excel SUMIF Function?

The SUMIF function calculates the sum of the values based on a single condition.

The syntax for it,

=SUMIF(range, criteria, [sum_range])

Here,

• 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.

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

For example, to calculate the sum of all the numbers less than 10, you can use the formula:

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

Example of the 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.

How to Apply the SUMIF Function?

Here are the steps to apply the SUMIF function in Excel:

1. Start with inserting an equal sign (=) in a blank cell.
2. Then type SUMIF.
3. Type open parenthesis “(“.
   As you type the open parenthesis, Excel shows the syntax of the SUMIF function.
   
   
4. Insert a cell range where you want to evaluate your condition.
   For this instance, I’m inserting the range A2:A5.
5. Then insert a comma (,).
   After inserting the comma, Excel will ask you for the second argument which is criteria.
   
   
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.
7. Then insert a comma (,) again.
   
   
8. Now insert the third argument which is sum_range.
   Here I’m inserting the range C2:C5.
9. Finally, add a closing parenthesis “)” to complete the syntax.
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!

Frequently Asked Questions

Can you use text in SUMIF?

Yes, you can use text in the SUMIF function in Excel. The SUMIF function allows you to sum values based on a specified condition, including conditions involving text.

Here’s a simplified guide:

=SUMIF(range, criteria, [sum_range])

• range: The range of cells to be evaluated based on the condition.
• criteria: The condition to be met.
• [sum_range]: (Optional) The actual cells to sum if the corresponding cells in the range meet the condition.

Example: =SUMIF(A2:A10, “Category1”, B2:B10)
In this example: A2:A10 is the range to be evaluated; “Category1” is the text-based condition. Then, B2:B10 is the sum range.

The function will sum the values in this range where the corresponding cells in A2:A10 match the condition “Category1.” Adjust the ranges and criteria based on your specific data. The SUMIF function is versatile and can handle text-based conditions efficiently in Excel.

Can you do SUMIFS with 3 criteria?

Yes, you can use the SUMIFS function with three criteria in Excel. The SUMIFS function allows you to sum values based on multiple conditions.

Here’s a simplified guide:

=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, criteria_range3, criteria3)

• sum_range: The range of cells to sum.
• criteria_range1, criteria_range2, criteria_range3: The ranges where the corresponding criteria are applied.
• criteria1, criteria2, criteria3: The conditions to be met for each criteria range.

Example: =SUMIFS(C2:C10, A2:A10, “Category1”, B2:B10, “ProductX”, D2:D10, “>500”)
In this example: C2:C10 is the sum range. A2:A10 is the first criteria range (Category) and B2:B10 is the second criteria range (Product).
D2:D10 is the third criteria range (Sales). “Category1“, “ProductX”, and “>500” are the corresponding criteria.

This formula calculates the sum in the range C2:C10 where Category is “Category1,” Product is “ProductX,” and Sales are greater than 500. Adjust the ranges and criteria based on your specific data.