If you only include the range without blanks, then this will do:
=SUM(A1:A100)/COUNTIF(A1:A100,"<0") --(no blanks in A1:A100)
otherwise use this:
=SUM(A1:A100)/SUMPRODUCT(N(A1:A100<0),N(LEN(A1:A100)0))

Bob Umlas
Excel MVP

"Alex" wrote:

Hello everyone,

I was wondering if there was a way of excluding Zeros from averages.
I have tried IF and AND but have not been able to get it to work correctly.

I want to be able to fist find column b, then get an average by group where
value in F is not Zero.

This is what I have (it's working, but I cant seem to be able to count only
if value < 0, using a countif)

=(SUMIF(B18:B65536,B8,F18:F72))/COUNTIF(B18:B65536,B8)

Sample data:

B C E F G H
Online Sales 329 138 1 150 1
Location Sales 999 18 2 130 1

Thanks