Ignore zero in subtotal formula
 

I have the following formula whice averages the cells in column G. However,
some of those cells show a zero, which I would like to ignore. What would I
need to add to my formula to accommodate this?

=SUBTOTAL(1,G:G)

Thanks in advance

Ignore zero in subtotal formula
 

Try this:

=SUM(G:G)/COUNTIF(G:G, "<0")

This sums the entire column, but only divides it by the non-zero
cells, thus giving a true average.

Thanks,
Rachel Garrett

Ignore zero in subtotal formula
 

Sarah,

You can ignore the count of zeros with the array formula
COUNT(IF(array0,array)). Use that as your denominator and the SUM function
as the numerator. You can do the same thing by using Subtotal functions, one
for the numerator and one for the denominator. Hope this helps.

"Sarah (OGI)" wrote:

I have the following formula whice averages the cells in column G. However,
some of those cells show a zero, which I would like to ignore. What would I
need to add to my formula to accommodate this?

=SUBTOTAL(1,G:G)

Thanks in advance