Table of Contents
The AVERAGEIF function in Excel allows users to calculate the average of a specific set of data based on certain criteria. This function can be further enhanced by using multiple ranges, which allows for a more comprehensive analysis of the data. By specifying different ranges and criteria, the AVERAGEIF function can provide a more accurate average for a specific set of data. This feature is particularly useful in situations where there are multiple categories or conditions that need to be considered. By utilizing the AVERAGEIF function with multiple ranges in Excel, users can effectively analyze and summarize data in a more efficient and accurate manner.
Excel: Use AVERAGEIF with Multiple Ranges
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.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use the AVERAGEIF function with multiple ranges in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-averageif-function-with-multiple-ranges-in-excel/
stats writer. "How can I use the AVERAGEIF function with multiple ranges in Excel?." PSYCHOLOGICAL SCALES, 27 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-averageif-function-with-multiple-ranges-in-excel/.
stats writer. "How can I use the AVERAGEIF function with multiple ranges in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-averageif-function-with-multiple-ranges-in-excel/.
stats writer (2024) 'How can I use the AVERAGEIF function with multiple ranges in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-averageif-function-with-multiple-ranges-in-excel/.
[1] stats writer, "How can I use the AVERAGEIF function with multiple ranges in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the AVERAGEIF function with multiple ranges in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.