Use AVERAGEIF across multiple cell ranges in a worksheet

Question

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?

Solution

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

Comments

[Raystafarian]

What is Sheet 1:Sheet 2? You want the same range from both sheets?