formula - variable that returns the last cell

[Phil Payne]

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]

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]

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]

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]

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]

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]

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