Can Excel dynamically plug into a formula the last row (rows are added every week)

[ExcelLoverMan]

I have a spreadsheet where one or two new rows are added every week.
When I summarize my report I pick from row 2 to row n (last row value).
The report has about 30 cells and currently I have to retype the last row number 30 times)
I wanted to see if I can code something so that my report is able to pick the last row automatically
as I add a row at the bottom.
I appreciate some detailed reply on how to accomplish that.
Thank you very much!
I love Mr. Excel!

 

[Canapone]

Hi,

one approach could be

=SUM(A2:INDEX(A2:A50,COUNT(A2:A50)))

Hope it's of some help

 

[Canapone]

Hi again,

if your database would have empty rows among numbers, in order to intercept position of last number in A2:A50

=IF(A2,SUM(A2:INDEX(A2:A50,LOOKUP(2,1/ISNUMBER(A2:A50),ROW(2:50)))),0)

Just for sharing other ideas.

Regards

 

[MARK858]

@ExcelLoverMan, can you not convert your data to a table then you can add data and it will automatically account for the number of rows for basic functions like Sum, Average, Count, Max, Min etc. ?

 

[ExcelLoverMan]

Hi folks:
Thanks for trying but I DO NOT NEED TO ADD: Here is one of the report's function:
=COUNTIFS($B$2:$B$207,$A$260,$C$2:$C$207,$C$269,$I$2:$I$207,$C$262)
WHAT I WANT IS TO HAVE THE "207" that appears 3 times stored in some cell and I want it to update when I enter a new row. In this
case the last row is 207. When I went a new row I want this formula to update to 208 so I won't have to physically go into it (30 times for the whole
report).

 

[MARK858]

Thanks for trying but I DO NOT NEED TO ADD

That is what happens when people need to guess because you haven't given the necessary info (please don't shout using capitals when it is info that you haven't previously given, its irritating).

Assuming that your data is numbers (again you haven't stated what your data actually is), then try the formula below...

=COUNTIFS($B$2:INDEX(B:B,MATCH(9.99999999999999E+307,B:B)),$A$260,$C$2:INDEX(C:C,MATCH(9.99999999999999E+307,C:C)),$C$269,$I$2:INDEX(I:I,MATCH(9.99999999999999E+307,I:I)),$C$262)

and no there shouldn't be a space between the 6 and the 9 in 269, that is how the boards software is making it display.

If you just want the row number in a different cell, then based on column B (and assuming your cells contain numbers again)...

=ROW(INDEX(B:B,MATCH(9.99999999999999E+307,B:B)))