Dynamic average_range by column text in AVERAGEIFS formula

G

g3lo18

New Member
Joined
Nov 2, 2013
Messages
35
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!
 
Last edited:

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
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 />
 
Upvote 0
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.
 
Upvote 0
@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*”?
 
Upvote 0
@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.
 
Upvote 0
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))))
 
Upvote 0
g3lo18 said:
@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.
Click to expand...

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.
 
Upvote 0
steve the fish said:
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))))
Click to expand...

Thank you! What if I have multiple criteria, is Tony, is not Lisa, is Frank, etc.
 
Upvote 0
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.
 
Upvote 0
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


steve the fish said:
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))))
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
YTD and Period Averages? Possible averageif with index/match?
Replies
2
Views
57
reacon84
R
P
  • Question Question
Return a value from a table that is in the top n percent of a person's trial scores
2
Replies
13
Views
967
Fluff
Fluff
J
  • Question Question
Dependent dynamic list with offset/countif formula and name manager
Replies
1
Views
695
juliecooper255
J
J
  • Solved
Calculate last 5 soccer team goals using data from another sheet on same workbook.
2
Replies
19
Views
2K
felixstraube
F
U
  • Question Question
Dynamic Macro for INDEX/MATCH Formula
Replies
1
Views
426
StephenCrump
S

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top