An Overview of SUMIFS Function | Microsoft Excel
The SUMIFS 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 SUMIFS function in Excel.
Objectives
The SUMIFS function calculates the sum of the values based on multiple conditions. For example, to calculate the sum of all the numbers that are less than 10 and greater than 5, you can use the formula:
=SUMIFS(E1:E9,E1:E9,"<10",E1:E9,">5")
Syntax
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Arguments
- sum_range: Compulsory. It refers to the cell range that you want to add up.
- criteria_range1: Compulsory. It refers to the range of the cells where you want to evaluate your first criteria.
- criteria1: Compulsory. It refers to the first condition based on which you want to add up values. Examples: 27, “<27”, D9, “8?”, “orange*”, “*~*”, or NOW().
- criteria_range2: Non-compulsory. It refers to the range of the cells where you want to evaluate your second criteria.
- criteria2: Non-compulsory. It refers to the second condition based on which you want to add up values. Examples: 27, “<27”, D9, “8?”, “orange*”, “*~*”, or NOW().
Output
A numerical value which is the summation of the sum_range argument.
Example of SUMIFS 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 product model.
Now to calculate the Total Sales amount for the Category, Smartphone, and brand, Apple, we can use the following formula using the SUMIFS function.
=SUMIFS(C2:C10,A2:A10,"Smartphone",B2:B10,"Apple*")
Here,
- C2:C10 is the sum_range argument.
- A2:A10 is the criteria_range1 argument.
- “Smartphone” is the criteria1 argument.
- B2:B10 represents the criteria_range2 argument.
- “Apple*” is the criteria2 argument.
Here, the SUMIFS function looks for “Smartphone” in the range A2:A10 and then looks for any model name that has the keyword “Apply” in the range B2:B10. Based on this condition, the SUMIFS function sums up the range C2:C10.
Usage Guide of SUMIFS Function
Step_1: Start with inserting an equal sign (=) in a blank cell.
Step_2: Then type SUMIFS.
Step_3: Type open parenthesis “(“.
As you type the open parenthesis, Excel shows the syntax of the SUMIFS function.
Step_4: Insert the sum_range where you want to perform the summation.
For this instance, I’m inserting the range C2:C6.
Step_5: Then insert a comma (,).
After inserting the comma, Excel will ask you for the second argument which is criteria_range1.
Step_6: Now insert criteria_range1.
Here I’m inserting the range A2:A6.
Step_7: Then insert a comma (,) again.
The next argument to insert is the criteria1 argument.
Step_8: Now insert the first criteria.
Here I’m inserting “Smartphone” which I want to choose from the Category column.
Step_9: Then insert a comma (,) again.
The next argument to insert is the criteria_range2.
Step_10: Now insert criteria_range2.
Here I’m inserting the range B2:B6.
Step_11: Then insert a comma (,) again.
The next argument to insert is criteria2.
Step_12: Now insert the second criteria.
Here I’m inserting “Apple*” which looks for text that has the keyword “Apple” in the Model column.
Step_13: Finally, add a closing parenthesis “)” to complete the syntax.
Step_14: Now, press ENTER.
The SUMIFS function will sum up all the sales amount that matches the product category Smartphone and the brand Apple.
Availability
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Remarks
- The SUMIFS function supports up to 128 criteria_range/criteria pairs.
- The conditions in the SUMIFS function work using the AND logic. This means criteria1 AND criteria2 AND criteria3 etc.
- Sum_range and criteria_range must have the same number of rows and columns. Any exception will return #VALUE! error.
- You must use double quotation marks (“”) to specify any text criterion or criteria having mathematical or logical expressions.
- You don’t need to use double quotation marks to specify numerical criteria.
- 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*”.
- You need not use quotation marks while using cell addresses in the criteria argument.
Conclusion
The SUMIFS function is one of the math and trigonometry functions in Excel. If you have any questions regarding the SUMIFS function, please comment below. Thanks!
