• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Define Range by Last Row...is it possible with formula only?

PP3321

PP3321

Active Member
Please see attached.

I want to only count A3:A5 because A5 is the last row.

I know I can do this using VBA but I need to achieve this using formula only...
is it possible...?

Thank you...



Screenshot.png
 
AFAIK - You need volatile function (Be it INDIRECT() or OFFSET()), to use dynamic range in a formula. Though I tend to create dynamic named range and then use that in a formula.
 
1. Indirect Function is also volatile...? I did not know that...

2. When you have time, could you please share with me your method of using named range...?
 
shrivallabha said:
Following construct is semi-volatile i.e. it will get re-calculated when the workbook is opened. Functions below are non-volatile.
=COUNTA(A3:INDEX(A2:A1000,MATCH(9E+307,A2:A1000)))
Click to expand...
=COUNTA(A3:INDEX(A2:A1000,MATCH(9E+307,A2:A1000))) in this example is an arguable statement.

Reasons are :

1] The COUNTA function is used to count for non-empty cells, that is numeric + text value.

2] In this example, if Cell A7="A", the formula still return 3 (not 4)

3] INDEX(A2:A1000,MATCH(9E+307,A2:A1000)) --> this formula return the last numeric value only.

My proposal is :

=COUNTA(A3:INDEX(A2:A1000,MATCH(2,INDEX(1/(1-ISBLANK(A2:A1000)),))))

Regards
Bosco

Edit :

1] is used if the reference cells are number

=COUNT(A3:INDEX(A2:A1000,MATCH(9.99E+307,A2:A1000)))

2] is used if the reference cells are text

=COUNTA(A3:INDEX(A2:A1000,MATCH("zzzz",A2:A1000)))

3] is used if the reference cells are non-empty (numeric+text value)

=COUNTA(A3:INDEX(A2:A1000,MATCH(2,INDEX(1/(1-ISBLANK(A2:A1000)),))))
 
Last edited:
You must log in or register to reply here.
Back
Top