How do I reference the last row in a table for a formula?

Posted on 2011-10-06
Last Modified: 2012-05-12
Hello experts,

See attached example.  I want to develop a formula that selects all rows of data when the last row varies.  How do I do that?

Thanks, Gary
Question by:garyrobbins
    LVL 19

    Expert Comment

    the best way is to use a formula that counts the rows in a named range theres an example here

    LVL 10

    Assisted Solution

    Do you have other data in H1...H5 that you want to ignore?
    Otherwise, you can use H:H in place of H6:H16

    LVL 50

    Assisted Solution

    by:barry houdini
    What do you want to do with the formula?

    If you want a count or sum with a single criterion and you can use SUMIF or COUNTIF then usually it's easier to use the whole column. Excel automatically only uses the "used range" which means that this is still efficient......but with SUMPRODUCT or array formulas it's best to do what regmigrant says and define a "dynamic range". You could use that directly in the formula but that makes the formulas overly long

    regards, barry
    LVL 41

    Accepted Solution

    Since Sumproduct is an array function already, there is NO NEED to hit CTRL-SHIFT-ENTER to make it into an array function (only if you have functions that require that, that are embedded in Sumproduct like MAX or COUNT or IF, whatever, would that be needed).

    This is your dynamic range :

    You could create a range name like "mySumRange" and then do


    Otherwise, you should be able to do:


    Note the dynamic range is built around numeric values.  If the values are text, then replace the 99^99 with rept("z",20) and it will work for text ranges

    See attached.



    Author Closing Comment

    Thank you all for your prompt replies.  I appreciate learning about the dynamic range feature.

    Hope you find my point allocation equitable.

    I love EE...


    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now