Count including Blank Cells

[bhofsetz]

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]

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

Please Login or Register  to view this content.

[stevekirk]

hi,

one solution maybe

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

change range to suit
steve

[bhofsetz]

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]

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

If you always start at row 1:

Please Login or Register  to view this content.

if not:

Please Login or Register  to view this content.

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

[bhofsetz]

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!

[NBVC]

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,

Please Login or Register  to view this content.

which references entire column except last row.

[bhofsetz]

Excelent!

Thanks for the explination and correction to the formula for 2000 and 2003

[bhofsetz]

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

[NBVC]

Try:

Please Login or Register  to view this content.

confirmed with CTRL+SHIFT+ENTER

[bhofsetz]

Thanks for the clarification. I was able to use the formulas and information you provided.

Thanks again

[morenitsvineet]

Hi NBVC,

I tried both the formulas in my Macro in Excel 2007 but i am getting an error as mentioned below:

Expected List Separator or ).

Can you please guide me with it as i am newbie to excel automation.

[HaHoBe]

Hi, morenitsvineet,

welcome to ExcelForum. Did you realize that the thread you entered is nearly 7 years old?

According to Forum Rule 2 you should start your own thread and post the code youi triued.

Maybe itīs just that you did not use FormulaArray tro create the correct formula but hard to tell with as little informationm as provided.

Ciao,
Holger