Use AVERAGEIF across multiple cell ranges in a worksheet

I am using AVERAGEIF using the following to return the answer

=AVERAGEIF('Sheet 2'!H3:K10,I9,'Sheet 2'!K3:K10)

This works well if I have all the information in one sheet, however I have the information in two sheets it no longer works. I have tried creating a SUPER RANGE as it work using contiguous cells in both sheets. I have been asked not to combine the two sheets.

=AVERAGEIF('Sheet 1:Sheet 2'!H3:L10,I9,'Sheet 1:Sheet 2'!K3:K10)

However I get a #VALUE error. This is potentially because for that particular row there is no match for I9 in one of the sheets.

How do I resolve this?

1 Answer

Averageif won't take more than one of each criteria and averageifs won't take more than one range to average.

You can get around it by getting creative-

1 dog
2 cat
3 cat
4 dog
5 cat
1 cat
2 cat
3 dog
4 dog
5 dog
=AVERAGEIF(B1:B10,"cat",A1:A10)

results the same as

1 dog 1 cat
2 cat 2 cat
3 cat 3 dog
4 dog 4 dog
5 cat 5 dog
=(SUM(SUMIF(B1:B5,"cat",A1:A5),SUMIF(D1:D5,"cat",C1:C5))/SUM(COUNTIF(B1:B5,"cat"),COUNTIF(D1:D5,"cat")))