To effectively harness Excel's power for data management, it's essential to master its formulas and functions. One such function is the SUMIFS function, which allows you to sum values in a range based on multiple criteria. This function is particularly useful when you need to analyze data based on various conditions. It is a versatile tool that can be used in many scenarios, from calculating sales figures for specific products or regions to analyzing customer data based on demographics or buying patterns.

Table of Contents

Formula Syntax

=SUMIFS(Sum_Range,Criteria_Range1,Criteria1,Criteria_Range2,Criteria2…)

• Sum_Range (required) – The cells to be summed.
• Criteria_Range1 (required) – The range that is checked against Criteria1. Criteria_range1 and Criteria1 form a search pair that looks for specific criteria within the range. Once items in the range match the criteria, their corresponding values in Sum_range are summed.
• Criteria1 (required) – The criteria that determine which cells in Criteria_range1 to include. Examples include 32, “>32”, B4, “apples”, or “32”.
• Criteria_Range2, Criteria2, … (optional) – Additional ranges and their corresponding criteria.

You can include up to 127 range/criteria pairs.

Let's explore a few examples to better understand this function.

download excel workbookSUMIFS-in-Excel.xlsx

Total Sales Amount for a Product in a Region

In this example, we are calculating the total sales amount for product 1001 in the East region. Let's break it down step by step.

STEP 1: Enter the SUMIFS function in cell F3.

=SUMIFS(

STEP 2: Enter the first argument – Sum_range. We've selected the range C2:C89 as it contains all the sales figures.

=SUMIFS(C2:C89,

STEP 3: Enter the second argument – Criteria_range1. Here, we've selected B2:B89, which includes the sales region details. We'll apply the East region criteria to this range.

=SUMIFS(C2:C89,B2:B89,

STEP 4: Enter the third argument – Criteria1. We've entered “East” to sum the sales in the East region.

=SUMIFS(C2:C89,B2:B89,”East”,

STEP 5: Enter the fourth argument – Criteria_range2. Here, we've selected A2:A89, which contains the product ID details. We'll apply the product 1001 criteria to this range.

=SUMIFS(C2:C89,B2:B89,”East”,A2:A89,

STEP 6: Enter the fifth argument – Criteria2. We've entered “1001” to sum the sales of product 1001.

=SUMIFS(C2:C89,B2:B89,”East”,A2:A89,”1001″)

As demonstrated, the SUMIFS function calculates the total sales of product 1001 in the East region by applying multiple criteria simultaneously.

Total Sales for a Product in 2 Regions

Here, we will calculate the sales for product 1002 in the North and South regions in cells F3 and G3. Let's go through this step by step.

STEP 1: Enter the SUMIFS function in cell F3.

=SUMIFS(

STEP 2: Enter the first argument – Sum_range. We've selected C2:C89, which contains all the sales figures we want to sum.

=SUMIFS(C2:C89,

STEP 3: Enter the second argument – Criteria_range1. Here, we've selected A2:A89, which contains the product ID details. We'll apply the product 1002 criteria to this range.

=SUMIFS(C2:C89,A2:A89,

STEP 4: Enter the third argument – Criteria1. We've entered “1002” to sum the sales of product 1002.

=SUMIFS(C2:C89,A2:A89,”1002″,

STEP 5: Enter the fourth argument – Criteria_range2. Here, we've selected B2:B89, which contains the region details. We'll apply the North and South criteria to this range.

=SUMIFS(C2:C89,A2:A89,”1002″,B2:B89,

STEP 6: Enter the fifth argument – Criteria2. We've entered {“North”, “South”} to sum the sales in the North and South regions separately in cells F3 and G3.

=SUMIFS(C2:C89,A2:A89,”1002″,B2:B89,{“North”,”South”})

Ensure to use curly brackets for your criteria.

As shown, the SUMIFS function calculates the total sales of product 1002 in the North and South regions by applying multiple criteria at once.

This can also be achieved by using two separate SUMIFS functions, as we did in the first example.

Total Sales when Sales Amount is Greater Than $20,000

In this example, we are calculating the total sales amount for product 1001, but only for transactions where the sales amount exceeds $20,000.

STEP 1: Enter the SUMIFS function in cell F3.

=SUMIFS(

STEP 2: Enter the first argument – Sum_range. We've selected C2:C89, which contains all the sales figures we want to sum.

=SUMIFS(C2:C89,

STEP 3: Enter the second argument – Criteria_range1. Here, we've selected A2:A89, which contains the product ID details. We'll apply the product 1001 criteria to this range.

=SUMIFS(C2:C89,A2:A89,

STEP 4: Enter the third argument – Criteria1. We've entered “1001” to sum the sales of product 1001.

=SUMIFS(C2:C89,A2:A89,”1001″,

STEP 5: Enter the fourth argument – Criteria_range2. Here, we've selected C2:C89, which contains the sales amount. We'll apply the rule – greater than $20,000 to this range.

=SUMIFS(C2:C89,A2:A89,”1001″,C2:C89,

STEP 6: Enter the fifth argument – Criteria2. We've entered >20000 to sum the sales for transactions where the sales amount is greater than $20,000.

=SUMIFS(C2:C89,A2:A89,”1001″,C2:C89,”>”&20000)

Conclusion

As demonstrated, Excel calculates the total sales amount for transactions where the product ID is 1001 and the transaction amount exceeds $20,000.

While the SUMIFS function is highly beneficial, it comes with certain limitations that you should be aware of:

• Range size: The SUMIFS function may not be suitable for some complex data analysis scenarios as it can become slow or cumbersome when used with very large datasets.
• Although the SUMIFS function supports multiple criteria, it may not capture all the nuances of complex datasets, such as interactions between different variables.
• Syntax complexity: Using the SUMIFS function can be challenging, especially when dealing with multiple criteria or complex datasets. A thorough understanding of Excel's features and syntax is necessary for its effective use.
• The size of each range must be consistent. If the provided ranges do not match, a #VALUE error will be returned.
• All range arguments must be actual ranges; arrays cannot be used with the SUMIFS function.
• SUMIFS is not case-sensitive.

The above is the detailed content of How to Use SUMIFS Function in Excel. For more information, please follow other related articles on the PHP Chinese website!