Creating Average IF

  • Thread starter Thread starter AAA1986
  • Start date Start date
A

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.
 
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))
 
If you meant not 03:06 but O3:O6, your formula would be
=SUMPRODUCT(--(O3:O6=18),I3:I6)/COUNTIF(O3:O6,18)
 
=IF(COUNTIF(I3:I6,18)=0,"",SUMIF(I3:I6,18)/COUNTIF(I3:I6,18))

simplify version:

=IF(COUNTIF(I3:I6,18),18,"")
 
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.
 
Back
Top