The SUMIFS function in Excel is a powerful tool used for adding up values in a range based on multiple criteria. It extends the SUMIF function to allow for multiple criteria.
Table of Contents
SUMIFS Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- sum_range: The range of cells to be summed up if they meet the specified criteria.
- criteria_range1: The first range to be evaluated by the corresponding criteria.
- criteria1: The condition that must be met in the criteria_range1.
- [criteria_range2, criteria2], …: Additional ranges and their corresponding criteria. Up to 127 range/criteria pairs can be specified in SUMIFS.
Each criteria_range must be the same size and shape as sum_range. The function sums up the values in sum_range where the corresponding cells in each criteria_range satisfy the given criteria.
Example
For example, to sum the sales amounts in a specific region for a particular product:
SUMIFS(total_sales, regions, "North", products, "Widget")
In this example, total_sales is the range containing the sales amounts, regions is the range containing region names, “North” is the criterion for the region, products is the range containing product names, and “Widget” is the criterion for the product.
The SUMIFS function is extremely useful for complex data analysis tasks where you need to sum values based on multiple conditions, often used in financial, statistical, and various data analysis scenarios.
Example 1: Calculate the sum of sales for a specific region and product
Formula: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,F2)
Result: 300
Example 2: Calculate the sum of sales for a specific month and product
Formula: =SUMIFS(C2:C5,A2:A5,E2,B2:B5,F2)
Result: 100
Example 3: Calculate the sum of sales for a specific range of dates and product
Formula: =SUMIFS(C2:C7,A2:A7,”>”&A2,A2:A7,”<“&A6,B2:B7,B2)
Result: 150
Example 4: Calculate the sum of sales for a specific range of numbers and product
Formula: =SUMIFS(C2:C5, A2:A5, “>”&A2, A2:A5, “<“&A5, B2:B5, F2)
Result: 150
Example 5: Calculate the sum of sales for multiple products and regions
Formula: =SUM(SUMIFS(C2:C7, A2:A7,{“East”,”West”},B2:B7,”A”))
Result: 300
Example 6: Calculate the sum of sales for a specific text string and product
Formula: =SUMIFS(C2:C7, B2:B7, F2, A2:A7, E2)
Result: $220
Example 7: Calculate the sum of sales for a specific date and text string
Formula: =SUMIFS(C2:C8,B2:B8,F2,A2:A8,”>”&A3)
Result: 500
Example 8: Calculate the sum of sales for blank cells and specific text string
Formula: =SUMIFS(C2:C7,A2:A7,E2,B2:B7,””)
Result: 450
Example 9: Calculate the sum of sales based on multiple criteria using logical operators
Formula: =SUMIFS(D2:D8, A2:A8, “A”, B2:B8, “<20”, C2:C8, “>”&C3)
Result: 300
SUMIF vs SUMIFS in Excel
SUMIF Function
Purpose: Used to sum cells based on a single condition.
Syntax:
SUMIF(range, criteria, [sum_range])
- range: The range of cells to apply the criteria against.
- criteria: The condition that defines which cells will be added.
- [sum_range]: The actual cells to sum (optional).
Example:
SUMIF(A1:A10, ">20", B1:B10)
sums the values in B1:B10 where corresponding cells in A1:A10 are greater than 20.
SUMIFS Function
Purpose: Used to sum cells based on multiple conditions.
Example:
SUMIFS(B1:B10, A1:A10, ">20", C1:C10, "<50")
sums the values in B1:B10 where corresponding cells in A1:A10 are greater than 20 and those in C1:C10 are less than 50.
Key Differences
- Number of Criteria: SUMIF evaluates one condition, while SUMIFS can evaluate multiple conditions.
- Order of Arguments: In SUMIF, the sum range is the third and optional argument. In SUMIFS, it is the first and mandatory argument.
- Flexibility: SUMIFS is more flexible and powerful, suitable for complex conditional summing scenarios.