Gone formula blind -cant get OFFSET formula to work

I cant see where I am going wrong here. If I change to I5:I732 it works great, however I need it to work over a wider array. It just returns #VALUE

=SUMPRODUCT(SUBTOTAL(3,OFFSET(I5,ROW(I5:N732)-ROW(I5),0)),--(I5:N732<>0))

Any idea?

Thanks

Try

=SUMPRODUCT(SUBTOTAL(3,OFFSET(I5,ROW(I5:N732)-ROW(I5),0))*(I5:N732<>0))

Thanks Jonmo - works brilliantly now.

I am sure my work around is very clunky for what I want to do, but all working now

Basically I just wanted a mean average of a filterable array I5:N732 which ignored 0

Glad to help..

But it's my opinion that once you have a need for 1 formula that ignores hidden rows, you're very likely to have another, then another etc..
Using that subtotal syntax in each formula becomes very expensive.
Particularly since you mentioned you're doing an Average. That essentially will need to use the syntax twice
Sumproduct(fortheSUM)/Sumproduct(fortheCOUNT)
That will be doing the work of the subtotal function twice.

I prefer to use a single helper column for the subtotal function, then refer to that column in a sumproduct (or any of the xxIFs functions)

Say column O for example.
O5: =SUBTOTAL(3,N5)
Fill that down to O732
This column gets filled with 1's and 0's (1 if row is visible, 0 if it is not)

Now use
=SUMPRODUCT(O5:O732*(I5:N732<>0)) to get your count of non 0's

And you can then hide the helper column, and refer to it in any other formula you have that would need to ignore hidden rows.

Upload file showing what is required.