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 other functions. This article will give you an overview of the SUMIFS function in Excel.

Table of Contents

What is the SUMIFS Function?

The SUMIFS function calculates the sum of the values based on multiple conditions.

The syntax with SUMIFS function:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Here,

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().

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")

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.

Steps to Use the SUMIFS Function

As you know the details of the SUMIFS function in Excel, now go through these steps below to use the function:

Start with inserting an equal sign (=) in a blank cell.
Then type SUMIFS.

Type open parenthesis “(“.
As you type the open parenthesis, Excel shows the syntax of the SUMIFS function.
Insert the sum_range where you want to perform the summation.
For this instance, I’m inserting the range C2:C6.
Then insert a comma (,).
After inserting the comma, Excel will ask you for the second argument which is criteria_range1.

Now insert criteria_range1.
Here I’m inserting the range A2:A6.
Then insert a comma (,) again.
The next argument to insert is the criteria1 argument.
Now insert the first criteria.
Here I’m inserting “Smartphone” which I want to choose from the Category column.

Then insert a comma (,) again.
The next argument to insert is the criteria_range2.
Now insert criteria_range2. Here I’m inserting the range B2:B6 and then, insert a comma (,) again.
The next argument to insert is criteria2.
Now insert the second criteria.
Here I’m inserting “Apple*” which looks for text that has the keyword “Apple” in the Model column.

Finally, add a closing parenthesis “)” to complete the syntax and press ENTER.

The SUMIFS function will sum up all the sales amount that matches the product category Smartphone and the brand Apple.

Availability

Excel for Microsoft 365	Excel 2016
Excel for Microsoft 365 for Mac	Excel 2016 for Mac
Excel for the web	Excel 2013
Excel 2021	Excel 2010
Excel 2021 for Mac	Excel 2007
Excel 2019	Excel for Mac 2011
Excel 2019 for Mac	Excel Starter 2010

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!

Frequently Asked Questions

What is the difference between SUMIF and SUMIFS?

The main difference between SUMIF and SUMIFS in Excel lies in the number of criteria they can handle.

SUMIF:
Handles a single condition. Syntax for it, =SUMIF(range, criteria, [sum_range])
Example: =SUMIF(A2:A10, “Category1”, B2:B10)
This formula sums values in B2:B10 where corresponding cells in A2:A10 meet the condition “Category1.”
SUMIFS:
Handles multiple conditions. Syntax of SUMIFS is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
Example: =SUMIFS(C2:C10, A2:A10, “Category1”, B2:B10, “ProductX”)
This formula sums values in C2:C10 where cells in A2:A10 match “Category1” and cells in B2:B10 match “ProductX.”

In summary, use SUMIF when dealing with a single condition, and opt for SUMIFS when you have multiple conditions to consider simultaneously. The added flexibility of SUMIFS makes it a powerful tool for complex data analysis in Excel.

Is SUMIFS better than VLOOKUP?

The choice between SUMIFS and VLOOKUP depends on the specific task. Generally, SUMIFS is better for summing values based on multiple conditions, whereas VLOOKUP is suitable for retrieving a single corresponding value.

SUMIFS: Ideal for summing values with multiple criteria. Its sytax is =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

VLOOKUP: Used for looking up a single corresponding value based on a specific condition. Syntax for VLOOKUP function is =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Considerations:

Use SUMIFS when dealing with multiple criteria and you need to sum corresponding values.

Use VLOOKUP when you want to find and retrieve a single value from a table based on a specific condition.

If you’re looking to return an entire column or row of values based on a condition, INDEX and MATCH may be more flexible than VLOOKUP.
Ultimately, the choice depends on the complexity of your data and the specific task at hand. Each function has its strengths, and the decision should align with your analytical needs in Excel.

Do SUMIFS make Excel slow?

The performance of SUMIFS in Excel depends on various factors such as the size of the dataset, complexity of the conditions, and overall spreadsheet complexity. While SUMIFS itself is not inherently slow, using it extensively on large datasets or within complex workbooks can contribute to slower Excel performance.

Rate this post