COUNTIF WITH OFFSET

[AVP68]

Hello,

I have a the following formula in row I57, COUNTIF(I6:I56,"<>0), which works fine, but that column will eventually have more data and I want to make it dynamic so I decided to place the new formula to row I4 which is one row above the header (in I5). So anything from I6 and downwards I need to do the count.

So I tired in I4 : =COUNTIF(OFFSET($I$5,1,,COUNTA($I:$I),1),"<>0") - not getting the result and even experimented with =COUNTIF($I$6:INDEX(I:I,I1),"<>0").

Any ideas?

Thanks,

Jay.

[#2 · Accepted solution]

If I1:I3 are blank, you may define CountRange, while I4 is selected, with this formula:
=I6:INDEX(I6:I1048576,COUNTA(I6:I1048576))
Thereafter, you may then enter this formula in I4:
=COUNTIF(CountRange,"<>0")

[#3]

@Twifoo  Thanks!

[#4]

You’re welcome.