Count of blank cells in a worksheet

[Ludwig]

The answer initially seems simple .. except that COUNTBLANK(C:C) returns a count of the [empty] cells in the column beyond the end of the actual data.

For example, my test worksheet has data in rows 1 thru 25, except for rows 1, 2 & 9 being blank. The formula =COUNTA(C:C) correctly returns 22. Not other columns have data beyond Column 25 - amd if I do Ctrl-End I get positioned on cell Y25, clearly Excel knows there are is more data in any row beyond row 25.

However the formula =COUNTBLANK( C:C ) returns 1048554, not 3 as I expect / want.

The reason I want this is to determine if any data is missing in column C after the heading rows; rows 1-3 are "heading" rows so I want to eliminate them from the check. That is, I want to test that all rows from row 4 to the end have a value. Simple formula for values existing is =COUNTA( C:C ) - COUNTA( C$1:C3 ).

I was hoping counting blanks using =COUNTBLANK( C:C ) - COUNTBLANK( C$1:C3 ) would have given me the answer 1 in this example. I would display this reuslt at the top of the worksheet so I can alert the user to something being wrong if it was > 0.

Sadly COUNTBLANK is not doing it for me. Does anyone know a simple way (ideally not an array formula, & definitely not VBA - needs to work when using Excel Online) to achieve the result I want? I was wondering if SUMPRODUCT could do it but I have no experience with it, yet.

Many thanks.

 

[arthurbr]

perhaps =COUNTBLANK($C$4:INDEX(C:C,match(9.9999e307,C:C))) (if cells contain numbers)
Otherwise =COUNTBLANK($C$4:INDEX(C:C,match(rept("Z",255),C:C)))

 

[James006]

Hello,

The sumproduct equivalent of countblank is as follows

=SUMPRODUCT(--(A1:D6=""))

Main question is your definition of Blank ... :wink:

 

[Ludwig]

=COUNTBLANK($C$4:INDEX(C:C,match(rept("Z",255),C:C)))

Thanks, that's works brilliantly! I only need to get my head around the formula .. I understand the principle, gotta lock it in to the brain for future use whenever that turns up ;-}

Is there a version that will count blanks as "BLANK" as well? i.e. how could I count cells where person has enter a space (1 or more) in a cell in the column C:C?

 

[Ludwig]

Hello,

The sumproduct equivalent of countblank is as follows

=SUMPRODUCT(--(A1:D6=""))

Main question is your definition of Blank ... :wink:

Thanks, but that has the same problem that COUNTBLANK(C:C) has .. answer is that large number.

 

[James006]

Hello,

Just to clarify ...

Your question was not about CountBlank ...

BUT about How to get the Last Row in Column which contains text ... :wink:

 

[arthurbr]

COUNTBLANK counts cells which are truly blank AND cells containing a null string ("") which is different from a space
You probably will have to add a COUNTIF
=COUNTBLANK($C$4:INDEX(C:C,match(rept("Z",255),C:C)))+COUNTIF($C$4:INDEX(C:C,match(rept("Z",255),C:C))," ")

(there should be something more elegant though...)

 

[Ludwig]

Actually, the bigger picture is "both" getting the last row with text AND counting how many blank/null cells there are in the column.