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").
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")
Best Response confirmed by
AVP68 (Occasional Contributor)