using value of last row in formula

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

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
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.

Saqib Husain, SyedEngineerCommented:
=sum(indirect("a2:a"&E1))
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
If E1 returns the last row used in col. A, you may try something like this....
=SUM(A2:INDEX(A:A,E1))

Open in new window


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))

Open in new window


See which one returns the desired output depending what is returned by the cell E1.
[ fanpages ]IT Services ConsultantCommented:
If there was going to be a value within every row of column [A] up until the row you wished to total, you could replace your pseudo-formula:
=SUM(A2:A(lastow))

With:
=SUM(OFFSET(A2,0,0,COUNTA(A:A),1))
Why Diversity in Tech Matters

Kesha Williams, certified professional and software developer, explores the imbalance of diversity in the world of technology -- especially when it comes to hiring women. She showcases ways she's making a difference through the Colors of STEM program.

swjtx99Author Commented:
Hi Fanpages,

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 last row in column A that contains data as the range.

I am testing the previous two solutions now.

Thanks,

swjtx99
[ fanpages ]IT Services ConsultantCommented:
:)

Oh, OK, you could place a formula like the one below in cell [E1]:
=MAX(IFERROR(MATCH(2^99,A:A,1),0),IFERROR(MATCH(REPT(CHAR(255),99),A:A,1),0))
swjtx99Author Commented:
I got the last row number to be returned in cell Z5 on sheet 'Revo' using this formula:

=MATCH(REPT("z",255),'NewData'!C:C)

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

{=IFERROR(INDEX('NewData'!$D$2:$D$50000,MATCH(0, COUNTIF(B$13:B13,'NewData'!$D$2:$D$50000)+IF('NewData'!$A$2:$A$50000<>$C$1,1,0)+IF('NewData'!$C$2:$C$50000<>B$13,1,0),0)),"")}

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
[ fanpages ]IT Services ConsultantCommented:
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:A,1),0),IFERROR(MATCH(REPT(CHAR(255),99),A:A,1),0))

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 sktneerssaqibh?  I note you have not responded to either of them so far.

...

Like ssaqibh suggested above, are you using, say, the INDIRECT() function to combine the value in cell [Z5] of the [Revo] worksheet, or are you approaching the replacement of the array formula you have posted (in your most recent comment) with a different method?

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.
swjtx99Author Commented:
Hi Fanpages,

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
[ fanpages ]IT Services ConsultantCommented:
Hi 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.
swjtx99Author Commented:
Hi All,

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
swjtx99Author Commented:
Hi,

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
[ fanpages ]IT Services ConsultantCommented:
Will data rows be contiguous within columns [A], [C], & [D] within the [Raw Data] worksheet?

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

[rngData]=OFFSET('Raw Data'!$A$2,0,0,COUNTA('Raw Data'!$A:$A),1)
[rngLocation]=OFFSET('Raw Data'!$C$2,0,0,COUNTA('Raw Data'!$C:$C),1)
[rngName]=OFFSET('Raw Data'!$D$2,0,0,COUNTA('Raw Data'!$D:$D),1)

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(0, COUNTIF(B$3:B3,'Raw Data'!$D$2:$D$12024)+IF('Raw Data'!$A$2:$A$12024<>$C$1,1,0)+IF('Raw Data'!$C$2:$C$12024<>B$3,1,0),0)),"")

to:
=IFERROR(INDEX(rngName,MATCH(0, COUNTIF(B$3:B3,rngName)+IF(rngDate<>$C$1,1,0)+IF(rngLocation<>B$3,1,0),0)),"")

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 trial
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Whatever you are trying to achieve can be achieved with the help of a Pivot Table.
I 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
swjtx99Author Commented:
Hi FanPages,

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
swjtx99Author Commented:
Hi Sktneer,

I wish I could use a Pivot table. Thanks for the suggestion.

Regards,

swjtx99
swjtx99Author Commented:
Hi Fanpages,

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
[ fanpages ]IT Services ConsultantCommented:
No problem at all.

Good luck with the rest of your project.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.