find last cell in a column that has a value

M

Mitchell

I have a sheet that users can add lines. I need to determine the last
row/cell with a value. This is to be used in countif(ci6:(need to put in last
cell),"")

Thanks for the help.
 
S

Sloth

If you know the last value is a number you can use this formula.

=MATCH(999999999,CI:CI)

I am not exactly sure why it works, but it does.

To use with the countif you would use this formula.

=COUNTIF(INDIRECT("CI6:CI"&MATCH(999999999,CI:CI)),"")
 
S

Sloth

I suppose an easier way to get the same answer would be with this formula

=COUNTIF(A:A,"")-COUNTIF(A1:A5,"")
 
M

Mitchell

The column I'm checking has names in it. I have another column with numbers
(H) and can determine the last number of that column

countif(Tasks!ci6:MATCH(999999999,Tasks!H:H),"")

gives me an error message
 
S

Sloth

You need to use the INDIRECT Function like this

=COUNTIF(INDIRECT("Tasks!CI6:CI"&MATCH(999999999,CI:CI)),"")

But the second function I gave you should work in either case (numbers or
names), because it ignores everything below the last entry in that column.

=COUNTIF(A:A,"")-COUNTIF(A1:A5,"")
 
Top