Forum Discussion

Copper Contributor

Jan 13, 2020

Solved

Last number in a series?

Hi, I have a column of numbers that get's added to every day. I need a formula to return me the last number in the series please? Thanks in advance

Formulas and Functions

Riny_van_Eekelen
Jan 13, 2020
dc05_
As an alternative:
=INDEX(A:A,AGGREGATE(14,6,(ROW(A:A)*NOT(ISBLANK(A:A))),1),1)
This one allows for blank cells and it doesn't matter if you have a header or not. It returns the value of the last cell in the column that is NOT blank.

Riny_van_Eekelen

Platinum Contributor

Jan 13, 2020

dc05_

As an alternative:

=INDEX(A:A,AGGREGATE(14,6,(ROW(A:A)*NOT(ISBLANK(A:A))),1),1)

This one allows for blank cells and it doesn't matter if you have a header or not. It returns the value of the last cell in the column that is NOT blank.

Tucev
Copper Contributor
Dec 11, 2020
Riny_van_Eekelen
Great! I do want to put other things in that A column but about 30 Rows down, is there a way to stop the value counting at a specific row?
Thanks
Rush
- SergeiBaklan
  Diamond Contributor
  Dec 12, 2020
  Tucev
  
  As variant
  
  =INDEX(A:A,XMATCH(,A:A)-1)
  
  And it stops on first blank cell
dc05_
Copper Contributor
Jan 13, 2020
thank you very much