Count including Blank Cells

**bhofsetz** · 01-29-2008, 08:04 PM

I am trying to use a formula to count the number of rows in a worksheet including blank cells.

If I use
=COUNTA(A:A)
then it returns the count of cells which contain a value

If I use
=COUNTIF(A:A,"")
it will count the blank cells in column A within the current used range in Excel 2000

using the same COUNTIF formula returns a count of the blank cells in the entire column in Excel 2007.

Therefore in Excel 2000 I can use
=COUNTA(A:A) + COUNTIF(A:A,"") and get the correct count

In Excel 2007 the same formula returns a count of all the cells.

Any idea of a fomula which will work in Excel 2007 that will return the empty cells in the used range?
Even better would be a formula which will return the same count (used and empty cells) in both 2000 and 2007.

TIA

**davesexcel** · 01-29-2008, 08:48 PM

This code is counts the number of rows from A1 to the last used cell

 Sub CountRows()
Dim r As Range
Set r = Range("A1", Range("A65536").End(xlUp))
MsgBox r.Count
End Sub

**stevekirk** · 01-29-2008, 11:52 PM

hi,

one solution maybe

=COUNTBLANK(A1:A16)+COUNTA(A2:A16)

change range to suit
steve

**bhofsetz** · 01-30-2008, 02:05 PM

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

=COUNTBLANK(A:A)+COUNTA(A:A)

then it simply returns the count of the entire row.

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

=COUNTA(A:A)

returns 12 in either Excel 2000 or Excel 2007

=COUNTIF(A:A,"")

returns 4 in Excel 2000 but returns 65524 in Excel 2007 (in compatability mode)

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

=COUNTA(A:A)+COUNTIF(A:A,"")

But in Excel 2007 this same function gives 65536 (in compatability mode).

**NBVC** · 01-30-2008, 02:41 PM

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

**bhofsetz** · 01-30-2008, 03:05 PM

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!

Count including Blank Cells

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Count including Blank Cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions