formula - variable that returns the last cell

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
89
Is there a way, in a formula, to use a variable that returns the last cell?

I use countif a lot and find that changing each range to accommodate added rows is arduous and error prone!

For example I use the following to count the number of instances where the cell contents are greater than zero in a column of specified length

=COUNTIF(C9:C1543,">0")

Is there a way to avoid manually changeing "1543" to the new last row number, something like =COUNTIF(C9:C&[lastcell],">0") ?

I want to do this using a formula rather than VBA.

Thanks.
 

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
Do you have data below your last row in column C? If not, you can just do something like:

=COUNTIF(C9:C10000,">0")
 

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
89
Do you have data below your last row in column C? If not, you can just do something like:

=COUNTIF(C9:C10000,">0")
Hello bbott,

Yes, you've guessed it, I have data below the last row. Normally something I try to avoid doing but in this case can't.

Thanks for your response.

Phil
 

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
Try this array formula (CTRL+SHIFT+ENTER not just Enter):

=COUNTIF(INDIRECT("C9:C"&MATCH(TRUE,C9:C10000="",0)+8),">0")

Excel will surround the array formula with {} when entered correctly.
 

Phil Payne

Board Regular
Joined
May 17, 2013
Messages
89
Your formula does work down to the first blank row but cannot be spread across multiple columns easily (click & drag).

I have a solution:
By placing a blank row between the last data table row and my error checking area then setting the range of the 'countif' to include that blank row, I only have to remember to insert any/all additional rows on the empty row (therefor keeping above it) to make the formula range automatically expand and count them.

Thanks for taking the time to help me.
 

rayk8301

Board Regular
Joined
Aug 4, 2014
Messages
172
if you put your data in a table then name the data in column C something such as Names or SocialSecNumbers, then you can use that instead of C or C1453. Any data that gets added later on should fall within that column name in that table and should always include the added data.
For example =countif(SocialSecNumbers,">0")
 

bbott

Well-known Member
Joined
Feb 5, 2010
Messages
2,350
Your formula does work down to the first blank row but cannot be spread across multiple columns easily (click & drag).

I have a solution:
By placing a blank row between the last data table row and my error checking area then setting the range of the 'countif' to include that blank row, I only have to remember to insert any/all additional rows on the empty row (therefor keeping above it) to make the formula range automatically expand and count them.

Thanks for taking the time to help me.
That's true. I didn't know you would be copying it across. I realize you'll probably stick with your solution and this is a bit clumsy, but it works for copying. Again, this is an array formula.

=COUNTIF(INDIRECT(CHAR(COLUMN(C9)+64)&"9:"&CHAR(COLUMN(C9)+64)&MATCH(TRUE,C9:C10000="",0)+8),">0")
 

Forum statistics

Threads
1,082,577
Messages
5,366,427
Members
400,890
Latest member
Danni_8oii

Some videos you may like

This Week's Hot Topics

Top