Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

I find I often need to do something to a range on a worksheet and I am always using something like A2:A100 which is fine until I have 101 rows of data. Is there a way to find the last row automatically?

=Sum(A2:A(lastrow))

or some such?

Alternatively I have a formula to find the last row and put that row number in a cell so would this be a way to accomplish the same thing

=Sum(A2:A&"E1") where E1 contains a formula that calculates the number of the last row?

Thanks in advance,

swjtx99

=Sum(A2:A(lastrow))

or some such?

Alternatively I have a formula to find the last row and put that row number in a cell so would this be a way to accomplish the same thing

=Sum(A2:A&"E1") where E1 contains a formula that calculates the number of the last row?

Thanks in advance,

swjtx99

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

```
=SUM(A2:INDEX(A:A,E1))
```

If E1 returns the total values from the cell A2 down in col. A, you may try something like this...

```
=SUM(A2:INDEX(A:A,E1+1))
```

See which one returns the desired output depending what is returned by the cell E1.

=SUM(A2:A(lastow))

With:

=SUM(OFFSET(A2,0,0,COUNTA(

I guess "Sum" Was not the best example since what I really want is to know what the last row with data is and then reference that in another formula.

A better example would be if I wanted to count how many rows in column A contain the string "Pepsi" and I want to look at rows A2 through the

I am testing the previous two solutions now.

Thanks,

swjtx99

Oh, OK, you could place a formula like the one below in cell [E1]:

=MAX(IFERROR(MATCH(2^99,A:

=MATCH(REPT("z",255),'NewD

so what I want to do is replace 50000 in the following formula with the value in Cell Z5

{=IFERROR(INDEX('NewData'!

So everwhere there is a

$D$2:$D$50000

can I replace that with

$D$2:$D&'Revo'!Z5

Haven't been able to get it to work yet. Must be missing something.

Thanks,

swjtx99

Obviously none of the contributors to this thread to date has seen the data in column [C] of the [NewData] worksheet, but I would recommend you at least look at (or at least test/consider) the formula I posted above if you are planning on using the MATCH/REPT approach.

My formula takes account of both alphanumeric (text) data, & numeric data within the column:

=MAX(IFERROR(MATCH(2^99,A:

It may be the case that your data is purely text, so the formula you have posted (presumably after reading all of the previous comments within the thread), is suitable for your exact requirements, though.

Did you look at either of the other two proposals posted by

...

Like

The provision of a sample workbook with the two named worksheets you have mentioned, & sample/fabricated (note: not real/sensitive) data may help us determine why you cannot produce the results you are (now) seeking.

At the very least, please post the formula you have attempted to use for the replacement of "$D$2:$D$50000" with the "last row" formula result.

I also note you have "C$2:$C$50000" within that formula, so that is likely to need replacing too.

Thanks for your reply. The array formula I posted above works fine until my data exceeds 50000 rows, then I have to change all the 50000 to a higher number. I'm trying to avoid that by defining the range dynamically so my original question was is there a way to do that? D2:D? without having to resort to the dreaded D:D as that slows things down considerably.

The two possible solutions I thought of were either

1. finding the last row within a formula or

2. pointing the formula to a cell that contains the last row number

The two formulas I posted were "ideas". not actual problems.

I've been trying to modify my array formula based on both Saqib and Sktneer's examples but haven't yet been able to get either to work. Probably just an issue with an apostrophe or parenthesis somewhere but I've tried many versions without success.

The formula you posted to find the last row is excellent and much better than the one I was using since it works with any data type.

You're also right that posting an example worksheet is preferred so I will work on scrubbing one to post here as soon as possible. I didn't originally since I wasn't necessarily trying to solve one specific problem but more so trying to learn a method for defining an ever-changing range within a formula.

Thanks,

swjtx99

OK; creating a dynamic formula (probably within a named range that you can use within your formula instead of an explicitly defined address) will probably resolve the underlying query, but I will wait until you can provide a sample of the data just to be sure we are not missing anything pertinent.

Thanks for looking at this.

Just wanted to update those that have replied. Haven't had a chance to clean up a sheet to use as an example. Sorry for the delay.

swjtx99

Attached is an example sheet. The problem formula is in Cell B4 on the hours tab.

As I said before, it works until the number of rows exceeds the 50,000, so I would rather reference the "last row".

Thanks

ex1.xlsx

I assumed they would be, & added these three named ranges to your workbook:

[rngData]=OFFSET('Raw Data'!$A$2,0,0,COUNTA('Raw

[rngLocation]=OFFSET('Raw Data'!$C$2,0,0,COUNTA('Raw

[rngName]=OFFSET('Raw Data'!$D$2,0,0,COUNTA('Raw

I then changed the array formula you had defined in cell [B4] of the [Hours] worksheet from:

=IFERROR(INDEX('Raw Data'!$D$2:$D$12024,MATCH(

to:

=IFERROR(INDEX(rngName,MAT

I then copied this down column [ B ] from [B4] to [B13] (leaving cells [B14] to [B22] as you had previous defined them).

Finally, I added rows 12,025 to 60,002 with the [Raw Data] worksheet.

Here I placed some sample values in column [L] ("Tme2"), & matching criteria in columns [A] ("Data"; 23/08/2015), [C] ("Location"; Dallas), [D] ("Name"; single rows for Jennifer, Tom, Jeff, Robin, Robert, George, Chris, & Kelly, as well as multiple rows for Elsie), & [Q] ("Version"; A).

I also added some additional data for different dates, different Locations, different Names, & different Version (just to demonstrate these were not returned within the calculations performed by the formulae within the [Hours] worksheet).

The revised workbook is attached.

Q_28712652.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trialI have converted your Raw Data into an Excel Table by pressing Ctrl + T and inserted a pivot table as you can see on Pivot Hours Sheet.

If you look at it carefully, it is much more dynamic in comparison to the formula as you have various options to retrieve the data as per new criteria each time which is not possible with a formula as formulas are confined to the criteria ranges. This you will understand once you play with the pivot table filters or by adding more filters to the pivot table to retrieve data with many more criteria as well.

Also it is also advisable to consider pivot tables with large data set like yours if the data retrieval is possible with the help of pivot table.

swjtx99.xlsx

Good solution! I think it will work but I'm having trouble. I created the named ranges in my book and inserted the formulas into column B but it just returns blanks. I'm sure I'm doing something wrong but will keep trying to make this work.

Correct, all data in A, C and D are contiguous (no blank cells)

swjtx99

I found my error. Named a Range wrong (Data vs. Date). First real experience with names ranges and I can see how they can be very useful. Thanks for the help and your patience. Sorry this got dragged out.

Regards,

swjtx99

Microsoft Excel

From novice to tech pro — start learning today.

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.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with premium.
Start your 7-day free trial.