AverageIfS Array Problem?

[Zero_33]

Good evening all

I'm having trouble trying to get an AverageIFs array formula to work.

What i would like to do is an averageifs formula to work out the average if 2 requirements are met.

Condition 1 - IF REG column contains all the English regions (E, EM, GL, NE,NW, SE,SW,WM,YH)

Condition 2- IF - Call column cells contain values above 0 (">0")

Doing it manually by filtering, i get 65.83, but need a formula to do the same thing :/

I tried putting a formula together but i couldnt get it to work properly.

Im pretty sure its a simple solution but i cant figure it out right now :/

I even tried simplifying the problem in sheet 1, and i still couldnt make the formula get 2.75 (the value I get when averaging A XYZ with values over 0 manually).

This problem has been driving me crazy for hours, and could use a fresh set out eyes since no one at my office can use Excel :/

Let me know if any of you guys can figure out what I'm doing wrong in trying to get this formula to work.

https://drive.google.com/file/d/0B9T_Wv_IkX5aMG1xWndhVXpXMVk/view?usp=sharing

Thanks again!

 

[pgc01]

Hi
Welcome to the board

The average is equal to the sum of the values divided by the count.

Try, for ex.:

=SUMPRODUCT(SUMIF(B5:B394,J2:J10,C5:C394))/SUMPRODUCT(COUNTIFS(B5:B394,J2:J10,C5:C394,">0"))

with E, EM, GL, NE,NW, SE,SW,WM,YH in J2:J10

 

[Zero_33]

Hi thanks for the warm welcome.


I tried your suggestion, sadly i get a slightly different answer of 65.74, and not 65.83 which is the average i got by working it out manually.

 

[Zero_33]

For some reason, i cant edit or delete my last post.


Your solution worked, i missed of 1 cell in my criteria. But is there a reason why you can't use AVERAGEIFs formula in the same way?

 

[pgc01]

For some reason, i cant edit or delete my last post.


Your solution worked, i missed of 1 cell in my criteria. But is there a reason why you can't use AVERAGEIFs formula in the same way?

Yes.

AverageIfs() with an array calculates the averages for each element of the array.

If you want to know the average for the elements EM and the average for elements SE you can use AverageIfs() with the array {"EM","SE"}. This will give you the 2 averages.

In your case this is not what you want, you want the combined average for the elements in a list not the individual average for each element in the list. That's why you use the general formula for the average: sum/count