Ignore zero in subtotal formula

  • Thread starter Thread starter Sarah (OGI)
  • Start date Start date
S

Sarah (OGI)

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
 
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
 
Sarah,

You can ignore the count of zeros with the array formula
COUNT(IF(array>0,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.
 
Back
Top