Average ifs with multiple columns as the average range

I am sure the answer to this is already out there somwhere...

if I use average formula:
=AVERAGEIF('Customer Experience 16-17'!$B$2:$KB$22,"CAT",'Customer Experience 16-17'!$C$2:$K$22)

I get a result

however if i try and add another criteria using AVERAGEIFS
=AVERAGEIFS('Customer Experience 16-17'!$C$2:$KB$22,'Customer Experience 16-17'!$B$2:$B$22,"CAT",'Customer Experience 16-17'!$O$2:$O$22,"DOG")I get a VALUE error,

there has to be a way around this????

You seem to be using ranges of very different sizes. AVERAGEIFS likes the criteria and average ranges to be the same size. From the Excel help file:

"Unlike the range and criteria arguments in the AVERAGEIF function, in AVERAGEIFS each criteria_range must be the same size and shape as sum_range. "

HI sorry I did make a slight typo in the second formula it should read

=AVERAGEIFS('Customer Experience 16-17'!$C$2:$K$22,'Customer Experience 16-17'!$B$2:$B$22,"CAT",'Customer Experience 16-17'!$O$2:$O$22,"DOG")I get a VALUE error,

however the issue is till the same I am asking for a workaround for the problem you described - the average range (C2:K22) is not the same size as the criteria range as I want to average multiple columns based on two different criteria in different columns B2:22 & O2:22) this is what I am asking for a workaround for.

What is being averaged, though? If the row has CAT in column B and DOG in column O, where are the numbers that should be included in the average? It would help immensely if you could post a small sample file (Use: Go Advanced --> Manage Attachments) so I could see how your data is arrayed.

Hi, AlKey, I have tried this and it doesn't bring back the right answer? I have attached a test spreadsheet with the formula you have suggested in cell D 25 to demonstrate as far as I can see the average for this selection should be 4.5 but the formula returns 4.67?

CAntosh - please see the attachment the figures to be averaged are in columns C-K and the criteria are in columns B and O

Hi I should have said I only needed the average of the columns C-K L-M are irrelevant - left them in in case it made a difference. however I have tried the formula again with the C2:K22 range in and it works. one question just in case I need it. if I had to add a third criteria to the equation how would I do it?