Subtotal formula that excludes zeros

[GaryStone10]

Hi All

I have a formula here

=SUBTOTAL(1,B2:B32) which I use because I need to only total the visible cells in the range.

Is there a way in which I can modify this formula to ignore zero values within this range as well?

I know I could make a new set of columns with =If(B2<>0,B2,""), but I was wondering if there was a neater way.

Thanks in advance.
Gary.

 

[Andrew Poulsom]

Can't you autofilter column B for nonzero?

 

[GaryStone10]

Can't you autofilter column B for nonzero?

Unfortunately not, because I need the data in the surrounding columns.

 

[barry houdini]

To average the visible non-zeroes try

=SUBTOTAL(9,B2:B32)/SUMPRODUCT(--(B2:B32>0),SUBTOTAL(2,OFFSET(B2,ROW(B2:B32)-ROW(B2),0)))

 

[GaryStone10]

To average the visible non-zeroes try

=SUBTOTAL(9,B2:B32)/SUMPRODUCT(--(B2:B32>0),SUBTOTAL(2,OFFSET(B2,ROW(B2:B32)-ROW(B2),0)))

That works brilliantly, thanks very much.