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

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
Who is Participating?
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.


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

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

barry houdiniCommented:
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
garyrobbinsAuthor Commented:
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...

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.