Hi,
I am trying to obtain an average from multiple criteria using AVERAGEIFS function. The issue is I want to be able to specify the average range in a dynamic way based on column header name. I used index/match to specify the column name using header name for AVERAGEIF and it works like a charm. However, when using the same method in AVERAGEIFS it produces a #VALUE ! error. A simple example is attached. Please note there are two worksheets. Kindly advise if you have any solution to what I am trying to accomplish.
Many thanks!
g3lo18
worksheet "data example":
<tbody>
</tbody>
Main "Sheet2":
<tbody>
</tbody>
Thank you!
I am trying to obtain an average from multiple criteria using AVERAGEIFS function. The issue is I want to be able to specify the average range in a dynamic way based on column header name. I used index/match to specify the column name using header name for AVERAGEIF and it works like a charm. However, when using the same method in AVERAGEIFS it produces a #VALUE ! error. A simple example is attached. Please note there are two worksheets. Kindly advise if you have any solution to what I am trying to accomplish.
Many thanks!
g3lo18
worksheet "data example":
A | B | C | |
Tony | 1 | 2 | 3 |
Al | 1 | 2 | 3 |
Al | 1 | 2 | 3 |
Tony | 1 | 2 | 3 |
Lisa | 1 | 2 | 3 |
Lisa | 1 | 2 | 3 |
Tony | 1 | 2 | 3 |
Lisa | 1 | 2 | 3 |
Tony | 1 | 2 | 3 |
<tbody>
</tbody>
Main "Sheet2":
AVERAGEIF works | AVERAGEIFS does not | |||
Tony | Tony + Lisa | |||
A | 1 | =AVERAGEIF('data example'!A:A,"Tony",INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0))) | #VALUE ! | =AVERAGEIFS(INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0)),'data example'!A:A,"Tony",'data example'!A:A,"Lisa") |
B | 2 | =AVERAGEIF('data example'!A:A,"Tony",INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A4,'data example'!$A$1:$D$1,0))) | #VALUE ! | =AVERAGEIFS(INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A4,'data example'!$A$1:$D$1,0)),'data example'!A:A,"Tony",'data example'!A:A,"Lisa") |
C | 3 | =AVERAGEIF('data example'!A:A,Sheet2!$B$2,INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A5,'data example'!$A$1:$D$1,0))) | #VALUE ! | =AVERAGEIFS(INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A5,'data example'!$A$1:$D$1,0)),'data example'!A:A,"Tony",'data example'!A:A,"Lisa") |
<tbody>
</tbody>
Thank you!
Last edited: