formula - variable that returns the last cell

Phil Payne

Board Regular
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.

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Do you have data below your last row in column C? If not, you can just do something like:

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

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.

Phil

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.

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.

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

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

Replies
10
Views
946
Replies
0
Views
98
Replies
19
Views
261
Replies
1
Views
97
Replies
6
Views
132

1,196,374
Messages
6,014,900
Members
441,854
Latest member
Amstaff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back