Dynamic average_range by column text in AVERAGEIFS formula

[g3lo18]

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":

[TABLE="width: 256"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]A[/TD]
[TD="class: xl63, width: 64"]B[/TD]
[TD="class: xl63, width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tony[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Al[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Al[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tony[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Lisa[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Lisa[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tony[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Lisa[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
[TR]
[TD="class: xl63"]Tony[/TD]
[TD="class: xl63"]1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]3[/TD]
[/TR]
</tbody>[/TABLE]

Main "Sheet2":

[TABLE="width: 2161"]
<tbody>[TR]
[TD][/TD]
[TD]AVERAGEIF works[/TD]
[TD][/TD]
[TD]AVERAGEIFS does not[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Tony[/TD]
[TD][/TD]
[TD]Tony + Lisa[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]1[/TD]
[TD]=AVERAGEIF('data example'!A:A,"Tony",INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0)))[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD]=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")[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]2[/TD]
[TD]=AVERAGEIF('data example'!A:A,"Tony",INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A4,'data example'!$A$1:$D$1,0)))[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD]=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")[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]3[/TD]
[TD]=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)))[/TD]
[TD="align: center"]#VALUE ![/TD]
[TD]=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")[/TD]
[/TR]
</tbody>[/TABLE]


Thank you!

 

[DanteAmor]

Try this:

<b>data example</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /><col style="width:83.64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">A</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">B</td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">C</td><td > </td><td > </td><td style="background-color:#92d050; font-weight:bold; text-align:center; ">AVERAGEIF</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:left; ">Tony</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td > </td><td style="background-color:#ffff00; font-weight:bold; text-align:center; ">A</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:left; ">Al</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td > </td><td style="background-color:#b6dde8; ">Tony</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:left; ">Al</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td > </td><td style="background-color:#b2a1c7; ">Lisa</td><td style="text-align:right; ">1.7142857</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:left; ">Tony</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="text-align:left; ">Lisa</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="text-align:left; ">Lisa</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="text-align:left; ">Tony</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="text-align:left; ">Lisa</td><td style="text-align:right; ">3</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="text-align:left; ">Tony</td><td style="text-align:right; ">1</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >G4</td><td >=SUMPRODUCT(((A2:A10=F3)+(A2:A10=F4))*(B1:D1=F2)*(B2:D10))/SUMPRODUCT(((A2:A10=F3)+(A2:A10=F4))+0)</td></tr></table></td></tr></table> <br /><br />

 

[steve the fish]

You have two problems there. Your arrays need to be the same length with averageifs plus when you say this:

'data example'!A:A,"Tony",'data example'!A:A,"Lisa"

you are testing to see if cell A1 = Tony AND A1 = Lisa (then cell A2 the same etc.) This is clearly impossible. So you need to use something like:

=AVERAGE(IF(('data example'!$A$1:$A$10="Tony")+('data example'!$A$1:$A$10="Lisa"),INDEX('data example'!$A$1:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0))))

Enter this CTRL-SHIFT-ENTER.

 

[g3lo18]

@Steve_the_fish many thanks will give this a try and let you know. If I wanted to use not equal I can adapt this to do <>”text” correct? And, will wild cards work here? For example “<>*text*”?

 

[g3lo18]

@DanteAmor - thanks for the alternative. will try this if the response after yours doesn’t work. I think average is more straight forward and cleaner.

 

[steve the fish]

Here is how you could write cell doesnt contain Tony

=AVERAGE(IF(NOT(ISNUMBER(SEARCH("Tony",$A$2:$A$10))),INDEX('data example'!$A$2:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0))))

 

[DanteAmor]

@DanteAmor - thanks for the alternative. will try this if the response after yours doesn’t work. I think average is more straight forward and cleaner.

Of course, the average is more direct, but in this case you are looking for a cell that contains "Tony" or that contains "Lisa" and that you can get with a formula that handles arrangements, such as sumproduct.

Let me know if you have any doubt.

 

[g3lo18]

Here is how you could write cell doesnt contain Tony

=AVERAGE(IF(NOT(ISNUMBER(SEARCH("Tony",$A$2:$A$10))),INDEX('data example'!$A$2:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0))))

Thank you! What if I have multiple criteria, is Tony, is not Lisa, is Frank, etc.

 

[steve the fish]

If it is Tony it cant also be Lisa nor can it be Frank. Give examples of cell values and whether you want them counted in the average or not.

 

[g3lo18]

Couldn't get this to work with multiple criteria. Here is what I tried:


=AVERAGE(IF(NOT(ISNUMBER(SEARCH("Tony",$A$2:$A$10)))+(isnumber(search("lisa",$A$2:$A$10))),INDEX('data example'!$A$2:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0)))) it provides a number but the incorrect average

Here is how you could write cell doesnt contain Tony

=AVERAGE(IF(NOT(ISNUMBER(SEARCH("Tony",$A$2:$A$10))),INDEX('data example'!$A$2:$D$10,0,MATCH(Sheet2!$A3,'data example'!$A$1:$D$1,0))))