Average based on 2 criterias

[Malick]

Hi,

I am trying to get an average from my SCORE column based on 2 criteria; ignoring all 0s and taking only those values which are equal to my city in C2 cell. Can someone please help fix the below, I just can't get it right. Thanks.

=IF(Table1[City]=C2,SUM(Table1[ [SCORE] ]))/MAX(1,COUNT(Table1[ [SCORE] ])-COUNTIFS(Table1[SCORE],0,Table1[City]=C2))

 

[AlphaFrog]

Try this...

=AVERAGEIFS(Table1[SCORE], Table1[SCORE],"<>0",Table1[City], C2)


AVERAGEIFS function

 

[Malick]

Thanks mate, it works perfectly fine. That was so easy. Cheers.