Creating Average IF

[AAA1986]

I need the following function:

I want the average of I3:I6 only for records where 03:06 = 18.

Please help! Thanks in advance.

 

[Luke M]

umm...wouldn't the answer be 18 (or zero, if nothing meets criteria)?

=IF(COUNTIF(I3:I6,18)=0,"",SUMIF(I3:I6,18)/COUNTIF(I3:I6,18))

 

[David Biddulph]

If you meant not 03:06 but O3:O6, your formula would be
=SUMPRODUCT(--(O3:O6=18),I3:I6)/COUNTIF(O3:O6,18)

 

[Teethless mama]

=IF(COUNTIF(I3:I6,18)=0,"",SUMIF(I3:I6,18)/COUNTIF(I3:I6,18))

simplify version:

=IF(COUNTIF(I3:I6,18),18,"")

 

[AAA1986]

No, let me try again. To simplify: for all records that equal "18" in column
B, I want the average of column A.

For example:

A B
1 4 18
2 2 17
3 4 18

In the above table, I would only want the averages of A1 and A3 because both
rows have "18" in the B column. The average here would be 4.

Thanks.

 

[Smoking]

=SUMIF($B$1:$B$3,"=18",$A$1:$A$3)/COUNTIF($B$1:$B$3,"=18")

 

[AAA1986]

Perfect! Thank you.

=SUMIF($B$1:$B$3,"=18",$A$1:$A$3)/COUNTIF($B$1:$B$3,"=18")