The `SUMIFS` function in Excel is used to sum up values that meet multiple criteria. It allows you to specify one or more conditions to filter the data before performing the sum. Here’s a general guide on how to use the `SUMIFS` function:
Syntax
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- `sum_range`: The range of cells that you want to sum.
- `criteria_range1`: The range that you want to apply the first condition to.
- `criteria1`: The condition that defines which cells in `criteria_range1` will be summed.
- `criteria_range2, criteria2, …`: Additional ranges and conditions. You can add more conditions by repeating these arguments as needed.
Example
Suppose you have the following dataset:
| A | B | C |
|———-|———–|——-|
| Product | Category | Sales |
| ——– | ——— | —– |
| Apple | Fruit | 100 |
| Banana | Fruit | 150 |
| Carrot | Vegetable | 200 |
| Carrot | Vegetable | 250 |
| Apple | Fruit | 300 |
You want to sum the sales of “Fruit” products.
=SUMIFS(C2:C6, B2:B6, "Fruit")
- Formula:
In this formula:
- Result:
The formula would return 550, as it sums the sales of all products in the “Fruit” category (100 + 150 + 300).
Tips
- Criteria can be numbers, expressions, cell references, or text: For example, you can use `”>100″` to sum values greater than 100.
- Logical operators: The criteria argument can include logical operators like `>, <, =, <>`.
- Wildcards in text criteria: Use `*` to match any sequence of characters and `?` to match any single character.
- Ensure that `sum_range` and all `criteria_range`s are of the same size; otherwise, Excel may return an error or unexpected results.
Common Errors
- #VALUE! Error: This might occur if the `sum_range` or `criteria_range` are not the same size.
- #NAME? Error: Often due to misspelling the function name or using undefined named ranges.
Using the `SUMIFS` function can greatly enhance your ability to conduct conditional sums in Excel, helping you to analyze your data more effectively.
Unlock Your Potential
Excel
Access
Power BI
