The `AVERAGEIFS` function in Excel is used to calculate the average of cells that meet multiple criteria. It is particularly useful when you want to compute the average of values based on more than one condition. Here’s a step-by-step guide on how to use the `AVERAGEIFS` function:
Syntax
AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
- `average_range`: The range of cells that you want to average.
- `criteria_range1`: The first range where you want to apply the first condition.
- `criteria1`: The condition that defines which cells in `criteria_range1` will be considered.
- `[criteria_range2, criteria2], …`: Optional. Additional ranges and criteria. You can add more pairs of `criteria_range` and `criteria` as needed.
Example
Suppose you have a worksheet with the following columns:
- A: Product
- B: Region
- C: Sales
And you want to calculate the average sales for “Product A” in the “East” region.
Sample Data
| A | B | C |
|———-|———|——|
| Product | Region | Sales|
| Product A| East | 200 |
| Product B| West | 150 |
| Product A| East | 300 |
| Product B| East | 250 |
| Product A| West | 400 |
Formula
=AVERAGEIFS(C2:C6, A2:A6, "Product A", B2:B6, "East")
Steps to Use the Function:
- Select the Cell: Click the cell where you want the result to appear.
- Enter the Function: Type `=AVERAGEIFS(` to begin the function.
- Define the `average_range`: Select the range of cells that contains the values to be averaged (e.g., `C2:C6`).
- Set the First `criteria_range` and `criteria`: Select the range for your first criteria (e.g., `A2:A6`) and specify the condition (e.g., “Product A”).
- Set Additional Criteria (if necessary): Continue adding pairs of `criteria_range` and `criteria` as needed (e.g., `B2:B6`, “East”).
- Close the Parenthesis: Finalize your function with a closing parenthesis `)` and hit `Enter`.
Note:
- Criteria should be specified correctly: Text criteria should be enclosed in quotes, and logical operators should be combined with the criteria (e.g., `”>100″`).
- Consistent Sized Ranges: All criteria ranges must be of the same size as the `average_range`. If not, Excel will return an error.
With these steps, you can efficiently use the `AVERAGEIFS` function to analyze data based on multiple conditions!
Unlock Your Potential
Excel
Access
Power BI
