Adjustable column ref

[mole999]

I have seven day columns, with blanks and entries, I have a formula to identify the last ROW of each day, and use a MAX function to return the row number. so far so good

I need to use =COUNTBLANK(M14:S34)+COUNTA(M14:S34) and =COUNTA(M14:S34) to be able to identify the difference between filled and blanks, and this is where I am struggling.

i need to incorporate =INDIRECT("s3") = 34 in the above COUNTBLANK & COUNTA and whatever syntax something like =COUNTA(M14:"S"&INDIRECT(S3)) I think

i have tried I'm not getting the result i'm after.

Any pearls of wisdom that knows what I want

 

[FormR]

Hi, so in cell S3 you have 34 and want that to be used as the last row in the formula? If so you can try like this:

=COUNTBLANK(M14:INDEX(S:S,S3))

 

[mole999]

@FormR That works thank you, do we really need to reference the whole column when I already know where the value is stored, or have I missed something

 

[FormR]

do we really need to reference the whole column when I already know where the value is stored

There are no negative performance issues referencing the whole column here and it offers a certain level of robustness.

 

[mole999]

thank you