| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I combine spreadsheets and documents in one file? | Excel Discussion (Misc queries) | |||
| find a cell matching separate column and row values | Excel Worksheet Functions | |||
| insert date | Excel Worksheet Functions | |||
| run a macro in a locked cell | Excel Discussion (Misc queries) | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Linear Mode
