How to Use AVERAGEIF with Multiple Ranges in Excel
To use AVERAGEIF with multiple ranges in Excel, you cannot directly apply the AVERAGEIF function. However, you can achieve the desired results by using a combination of SUMPRODUCT and SUM functions. This approach will allow you to calculate the average of multiple ranges based on specific criteria.
Here's how to use AVERAGEIF with multiple ranges in Excel:
- Prepare your data in different ranges, making sure that each range has the same number of rows.
- Identify the criteria you want to use for the average calculation.
- Use the SUMPRODUCT function to calculate the sum of the values that meet your criteria in each range.
- Use the SUM function to count the number of cells in each range that meet your criteria.
- Divide the result of the SUMPRODUCT function by the result of the SUM function to get the average.
Example
Let's assume you have the following data in Excel:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| 10 | 15 | 20 | 25 | 30 | 35 |
| 40 | 45 | 50 | 55 | 60 | 65 |
| 70 | 75 | 80 | 85 | 90 | 95 |
Now, let's say you want to calculate the average of the values in the ranges A1:C3 and D1:F3, but only for the values that are greater than or equal to 50.
Here's the formula you would use:
=SUMPRODUCT((A1:C3>=50)*(A1:C3), (D1:F3>=50)*(D1:F3)) / SUM((A1:C3>=50)*(A1:C3>0), (D1:F3>=50)*(D1:F3>0))
This formula calculates the sum of the values greater than or equal to 50 in both ranges (A1:C3 and D1:F3) using SUMPRODUCT, and counts the number of cells that meet the criteria in both ranges using SUM. The result of SUMPRODUCT is then divided by the result of SUM to get the average.
In this example, the average of the values greater than or equal to 50 in the given ranges is 72.5.