Count including Blank Cells

I don't have 2007, but I think these should work:

If you always start at row 1:

=ROWS(A1:INDEX(A:A,MAX(MATCH(REPT("z",255),A:A),MATCH(9.9999999E+307,A:A))))

if not:

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

This last formula must, however, be confirmed with CTRL+SHIFT+ENTER not just ENTER. You will see { } brackets appear around the fomrula

Originally Posted by bhofsetz

Thanks NBVC

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.

Before XL2007, you can't use whole column references such as A:A with Array formulas...but it is okay in 2007.

so to make the second formula work in 2000 and 2003,

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

which references entire column except last row.

Try:

=ROWS(INDEX(A1:A65535,MATCH(TRUE,A1:A65535<>"",0)):INDEX(A1:A65535,MATCH(2,1/(A1:A65535<>""))))

confirmed with CTRL+SHIFT+ENTER