LinkBack URL
About LinkBacks
hi,
one solution maybe
change range to suit=COUNTBLANK(A1:A16)+COUNTA(A2:A16)
steve
hi,
one solution maybe
change range to suit=COUNTBLANK(A1:A16)+COUNTA(A2:A16)
steve
Thanks for the code Dave but I need to do this in a worksheet function rather than with code. Any suggestions?
Thanks for the suggestion Steve. However I need the function to work on the entire column and therefore do not know the range.
If I use
then it simply returns the count of the entire row.=COUNTBLANK(A:A)+COUNTA(A:A)
Does anyone know a way to count the cells including blank cells with a worksheet function?
i.e.
Col A could contian numbers and text and blank spaces <blank>
Heading 1
Heading 2
<blank>
test 1
<blank>
result
3
4
5
<blank>
test 2
<blank>
result
6
7
8
returns 12 in either Excel 2000 or Excel 2007=COUNTA(A:A)
returns 4 in Excel 2000 but returns 65524 in Excel 2007 (in compatability mode)=COUNTIF(A:A,"")
I need a function that will return 16 for the example column A shown in both Excel 2000 and Excel 2007
In Excel 2000 I can use
But in Excel 2007 this same function gives 65536 (in compatability mode).=COUNTA(A:A)+COUNTIF(A:A,"")
I don't have 2007, but I think these should work:
If you always start at row 1:
if not:
=ROWS(A1:INDEX(A:A,MAX(MATCH(REPT("z",255),A:A),MATCH(9.9999999E+307,A:A))))
This last formula must, however, be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the fomrula=ROWS(INDEX(A:A,MATCH(TRUE,A:A<>"",0)):INDEX(A:A,MAX(MATCH(REPT("z",255),A:A),MATCH(9.9999999E+307,A:A))))
Where there is a will there are many ways.
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Thanks NBVC
The first formula worked perfectly in Excel 2000 and 2007
(I haven't tried it in 2003 yet).
The second formula worked in Excel 2007
(once I remembered it was an array formula and used Ctrl+Shift+Enter to confirm it).
However the second formula returned a #NUM error in Excel 2000.
But since the first formula is working for my purposes I'll go with that.
Thanks agian!
Before XL2007, you can't use whole column references such as A:A with Array formulas...but it is okay in 2007.Originally Posted by bhofsetz
so to make the second formula work in 2000 and 2003,
which references entire column except last row.=ROWS(INDEX(A1:A65535,MATCH(TRUE,A1:A65535<>"",0)):INDEX(A1:A65535,MAX(MATCH(REPT("z",255),A1:A65535),MATCH(9.9999999E+307,A1:A65535))))
Excelent!
Thanks for the explination and correction to the formula for 2000 and 2003
One more issue I just came across.
If column A does not contain numbers in any of the cells then the formula does not work.
I know my example had numbers but there is a possibility that in use that column may contain only text.
Thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks