An overall average of two columns with criteria

[JeffFinnan]

I would like to get an overall average of two columns D6:D2000 and I6:I2000 that meet that meet the criterium =Cm in two other separate columns and the data columns do contain #N/A in some cells.

The individual averages using these formulas: =AVERAGEIFS(D6:D2000,D6:D2000,"<>#N/A", A6:A2000,"=Cm") and =AVERAGEIFS(I6:I2000,I6:I2000,"<>#N/A", K6:K2000,"=Cm") work correctly.

Thanks,
Jeff

 

[jasonb75]

The best way to do conditional averages over multiple ranges is to use the basic principle of average = sum / count.

=(SUMIFS(D6:D2000,D6:D2000,"<>#N/A",A6:A2000,"Cm")+SUMIFS(I6:I2000,I6:I2000,"<>#N/A",K6:K2000,"Cm"))/(COUNTIFS(D6:D2000,"<>#N/A", A6:A2000,"Cm")+COUNTIFS(I6:I2000,"<>#N/A", K6:K2000,"Cm"))