In Excel, it’s not possible to use the AVERAGEIF() function to calculate an average value using multiple ranges.
However, you can use the following formula as a workaround:
=(SUM(SUMIF(A2:A11,G2,B2:B11),SUMIF(D2:D11,G2,E2:E11))/SUM(COUNTIF(A2:A11,G2),COUNTIF(D2:D11,G2)))
This particular formula finds the average of the values in the ranges B2:B11 and E2:E11 where the corresponding values in the ranges A2:A11 and D2:D11 are equal to the value in cell G2.
The following example shows how to use this formula in practice.
Example: Using AVERAGEIF with Multiple Ranges in Excel
Suppose we have the following data in Excel that shows the sales of various fruits on different days:
Now suppose we would like to calculate the average daily sales of Mangos.
We can use the following formula to do so:
=(SUM(SUMIF(A2:A11,G2,B2:B11),SUMIF(D2:D11,G2,E2:E11))/SUM(COUNTIF(A2:A11,G2),COUNTIF(D2:D11,G2)))
We’ll type this formula into cell H2 and then press Enter:
We can see that the average daily sales of Mangos is 6.5.
We can verify this is correct by manually taking the average of all sales values where the corresponding product is Mangos:
Average Mango Sales: (8 + 6 + 5 + 4 + 8 + 8) / 6 = 6.5.
This matches the value that we calculated using our formula.
Note: In this example, we calculated an average using two cell ranges, but we can use similar syntax to include any number of cell ranges that we’d like.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Average If Not Blank
Excel: How to Calculate Average Excluding Outliers
Excel: How to Calculate the Average by Group