The SUMIFS function in Excel allows you to sum values based on multiple criteria. It’s particularly useful when you need to filter data based on certain conditions and then sum the corresponding values.
Here’s the syntax of the SUMIFS function:
=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
sum_range
: This is the range of cells that you want to sum.criteria_range1
: This is the range of cells that you want to apply the first criterion to.criteria1
: This is the condition that must be met incriteria_range1
.[criteria_range2, criteria2]
: These are optional additional ranges and criteria pairs. You can include up to 127 range/criteria pairs in total.
Here’s an example to illustrate how SUMIFS works:
Let’s say you have a dataset with the following information:
Item | Category | Quantity | Price |
---|---|---|---|
Apple | Fruit | 100 | 0.5 |
Banana | Fruit | 150 | 0.3 |
Carrot | Vegetable | 200 | 0.4 |
Orange | Fruit | 120 | 0.6 |
Now, you want to find out the total value of fruits where the quantity is greater than 100. You can use the SUMIFS function for this.
Assuming:
- The quantity range is in column C (C2:C5)
- The category range is in column B (B2:B5)
- The price range is in column D (D2:D5)
Your SUMIFS formula would look like this:
=SUMIFS(D2:D5, B2:B5, "Fruit", C2:C5, ">100")
This formula will:
- Sum the values in the Price column (D2:D5)
- Where the Category is “Fruit” (B2:B5)
- And the Quantity is greater than 100 (C2:C5)
So, it will return the total price for fruits with quantities greater than 100, which is 0.5 (Apple) + 0.6 (Orange) = 1.1.
That’s how SUMIFS function works in Excel! It’s a powerful tool for conditional summing based on multiple criteria.