The formula looks like this:

=AVERAGEIFS($H$2:$H$44482,$F$2:$F$44482,$AC23,$J$2:$J$44482,$Y$3:$Y$18)

Where:

H column is the average range.

F column is the criteria range 1

AC23 is the criteria1 for it to match

J column is the criteria range 2

With an array of column Y to be matched against.

The spilled result includes 2 DIV/0 errors (I presume there is no data for it to give an average). I don't want a spilled data result though - i'll like the average of all the results.

2 questions:

1) How do I get it to ignore DIV/0 errors?

2) Assuming you/I/we get this to work, will the result give me an arithmetic mean i.e. there will be no weighting applied to the contents of the array? Just a simple average between the results? How would you recommend a geometric mean instead?

thanks!