Averageif function with range that includes non-consecutive cells

[Daniel Collison]

I am trying to average a range of numbers if the values are less than or
equal to 5. The range includes non-consecutive cells. I have attempted two
different versions of the averageif formula (see below). Both versions
return a #VALUE error.

=AVERAGEIF((P6,R6,S6,T6,U6,V6,X6,Y6,Z6),"<=5")
=AVERAGEIF((P6,R6:V6,X6:Z6),"<=5")

Thanks,

 

[Jacob Skaria]

Alternative

=SUM(SUMIF(INDIRECT({"P6","R6:V6","X6:Z6"}),"<=5"))/
SUM(COUNTIF(INDIRECT({"P6","R6:V6","X6:Z6"}),"<=5"))

If this post helps click Yes

 

[T. Valko]

One way...

=(SUMIF(R6:V6,"<=5")+SUMIF(X6:Z6,"<=5")+(P6<=5)*P6)/INDEX(FREQUENCY((P6,R6:V6,X6:Z6),5),1)

 

[T. Valko]

Try this...

=(SUMIF(R6:V6,"<=5")+SUMIF(X6:Z6,"<=5")+(P6<=5)*P6)/INDEX(FREQUENCY((P6,R6:V6,X6:Z6),5),1)

 

[Daniel Collison]

Jacob: The formula worked great. However, the data for which the
calculation was intended spanned 1,400 rows. I tried to copy the formula to
the other 1,399 rows, but the reference to row 6 remained the same.

However, the formula that T. Valko posted worked great and I was able to
successfully copy to other rows.

Thanks for your input!

Daniel