AVERAGEIFS

[davin1130]

I have a file that keeps track of values and zones. I'm trying to use "AVERAGEIFS" to find the average of values in column "F" for only zones 1 & 3 in column "G". When i perform the action using AVERAGEIFS it creates 2 different averages … an average over F8:F17 for all G8:G17 having a value of "1" and an average over F8:F17 for all G8:G17 having a value of "3", INSTEAD of getting a single average for range F8:F17 with G8:G17 having values of 1 or 3 ??? What can i do to accomplish this. I've attached a snapshot of my file. I tried to use "SUM" then divide by 2 to see if i could get the same answer.

 

[steve the fish]

One of these maybe?

=AVERAGE(FILTER(F8:F17,(G8:G17=1)+(G8:G17=3)>0))
=SUMPRODUCT(--((G8:G17=1)+(G8:G17=3)>0),F8:F17)/SUMPRODUCT(--((G8:G17=1)+(G8:G17=3)>0))

 

[Fluff]

One option

=SUM(SUMIFS(F8:F17,G8:G17,{1,3}))/SUM(COUNTIFS(G8:G17,{1,3}))

or with that data

=AVERAGEIFS(F8:F17,G8:G17,"<>2")