Excel SUMIFS Function
The SUMIFS function in Microsoft Excel adds cells that meet multiple criteria across multiple ranges. It is useful for aggregating data based on several conditions, such as summing sales for a specific product and region.
Key Topics
- Overview of SUMIFS Function
- SUMIFS Function Syntax
- Implementation Examples
- Sample Dataset
- Key Takeaways
Overview of SUMIFS Function
The SUMIFS function extends SUMIF by allowing multiple criteria across different ranges, enabling precise summation. It is ideal for scenarios requiring totals based on multiple conditions, like financial or sales analysis.
SUMIFS Function Syntax
The syntax for the SUMIFS function is as follows:
Syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2], [criteria2], ...)
Parameters:
- sum_range: The range to sum (required).
- criteria_range1: The first range to evaluate (required).
- criteria1: The condition for the first range (required).
- criteria_range2, criteria2, ...: Additional ranges and criteria (optional).
Implementation Examples
Below are examples of how to use the SUMIFS function in different scenarios.
Example 1: =SUMIFS(B2:B10, C2:C10, "North", B2:B10, ">1000") — Sums B2:B10 where C2:C10 is "North" and B2:B10 is >1000.
Example 2: =SUMIFS(B2:B10, A2:A10, "Laptop", B2:B10, ">=500") — Sums B2:B10 where A2:A10 is "Laptop" and B2:B10 is >=500.
Example 3: =SUMIFS(B2:B10, A2:A10, "<>"", C2:C10, "South") — Sums B2:B10 where A2:A10 is non-empty and C2:C10 is "South".
Sample Dataset (Excel-style View)
Below is a demo dataset styled like Excel, showing how the SUMIFS function can be used to sum sales meeting multiple criteria.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Sales | Region | High Sales North Total |
| 2 | Laptop | 1500 | North | =SUMIFS(B2:B6, C2:C6, "North", B2:B6, ">1000") |
| 3 | Phone | 800 | South | |
| 4 | Laptop | 1200 | North | |
| 5 | Monitor | 900 | South | |
| 6 | Printer | 1100 | North |
Note: In the dataset, cell D2 uses =SUMIFS(B2:B6, C2:C6, "North", B2:B6, ">1000") to sum sales in B2:B6 where the region in C2:C6 is "North" and sales are greater than 1000. The formula is displayed only in D2 for clarity.
Key Takeaways
- The SUMIFS function adds cells meeting multiple criteria across multiple ranges.
- It extends SUMIF for precise summation, e.g.,
=SUMIFS(B2:B10, C2:C10, "North", B2:B10, ">1000"). - Criteria can include numbers, text, or expressions.
- Common uses include aggregating sales by multiple attributes, like product and region.
- The sample dataset demonstrates SUMIFS applied to sum high sales in the North region.