find last cell in a column that has a value
 

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.

find last cell in a column that has a value
 

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

"Mitchell" wrote:

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.

find last cell in a column that has a value
 

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

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

"Sloth" wrote:

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

"Mitchell" wrote:

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.

find last cell in a column that has a value
 

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

"Sloth" wrote:

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

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

"Sloth" wrote:

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

"Mitchell" wrote:

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.

find last cell in a column that has a value
 

You need to use the INDIRECT Function like this

=COUNTIF(INDIRECT("Tasks!CI6:CI"&MATCH(999999999,C I: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,"")

"Mitchell" wrote:

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

"Sloth" wrote:

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

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

"Sloth" wrote:

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

"Mitchell" wrote:

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.