AVERAGEIFS Average_Range Problems

[millonario101]

Thanks to any who have ideas on this one.

I'm using AVERAGEIFS for the first time, which I understand to be essentially the same as COUNTIFS but with a range of values preceding the criteria ranges and criteria.

I have a COUNTIFS function that works just fine. I need to now take an average of a range related to this COUNTIFS result. My problem is that the average_range preceding the COUNTIFS criteria produces "#VALUE!" if the range is on the same tab as the COUNTIFS criteria (it's coming from a large database on another tab). The fact that the database is on a different tab than the formula was not a problem for the COUNTIFS function, but it is for the AVERAGEIFS function.

If I move the average_range column that I'm trying to average to the same tab as the summary data, the funny thing is that this produces the correct average:

=AVERAGEIFS($CV$2:$CV$4500,'Ex.5.2 Complete Data'!$R$2:$R$4500,"="&$D$44,'Ex.5.2 Complete Data'!$N$2:$N$4500,"="&$B48,'Ex.5.2 Complete Data'!$CS$2:$CS$4500,">="&$F$44,'Ex.5.2 Complete Data'!$CS$2:$CS$4500,"<="&$H$44)

While having the average_range column in the original database produces #VALUE!

=AVERAGEIFS('Ex.5.2 Complete Data'!$CV$2:$CV$4501,'Ex.5.2 Complete Data'!$R$2:$R$4500,"="&$D$44,'Ex.5.2 Complete Data'!$N$2:$N$4500,"="&$B47,'Ex.5.2 Complete Data'!$CS$2:$CS$4500,">="&$F$44,'Ex.5.2 Complete Data'!$CS$2:$CS$4500,"<="&$H$44)

Any ideas? THANK YOU!

[daddylonglegs]

I think you just have the wrong size range. In this version

=AVERAGEIFS('Ex.5.2 Complete Data'!$CV$2:$CV$4501,'Ex.5.2 Complete Data'!$R$2:$R$4500,"="&$D$44,'Ex.5.2 Complete Data'!$N$2:$N$4500,"="&$B47,'Ex.5.2 Complete Data'!$CS$2:$CS$4500,">="&$F$44,'Ex.5.2 Complete Data'!$CS$2:$CS$4500,"<="&$H$44)

The range ends at row 4501, where other ranges are 4500 - mismatched ranges will give you #VALUE! error - change that one to 4500 and it should work OK

[millonario101]

This was it! Thank you! Brilliant - didn't know that ranges were such a big deal but it makes sense.

[MarvinP]

Hi millonario and welcome to the forum,

Count can deal with both Text and Numbers. Average will only work with numbers. Do you have some TEXT in the range you are trying to average?

Another possible problem is the criteria might need quotes around it. See http://www.ozgrid.com/Excel/count-if.htm for an example.