COUNTA counts blank cells 5

[krinid]

I've encountered a situation where COUNTA counts BLANK CELLS!

for example:
- cells A1 - A10 are blank
- len(A1) = 0
- IsBlank (A1) = true
- COUNTA(A1:A10) = 10

However, hitting delete on any of the cells causes it to be viewed as 'blank' by COUNTA. (eg: hitting delete [or clearing] A5 causes COUNTA(A1:A10) = 9, clearing another causes COUNTA(A1:A10) = 8, etc)

Any idea why this may be happening? Is there something else possibly in these cells with length 0? (nulls?)

 

[krinid]

My apologies, I've fibbed... I've double checked my work and the LENGTH of each cell is 0 but ISBLANK is FALSE. What could be in these cells that is length 0? (and can be cleared by hitting delete)

 

[xlbo]

a zero length string - usually the result of a formula like:

=IF(ISNA(VLOOKUP(A1,myRange,4,false)),"",VLOOKUP(A1,myRange,4,false))
Where the data is not found, it leaves a "" which is a zero length string but which means the cell is NOT empty nad they do get counted by countA

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[krinid]

Geoff,
Thanks! Very useful info.

fyi,
I've managed to incorporate your info into a working method to properly count it all using a function array:

={SUM(IF(A1:A10<>&quot;&quot;,1,0))}

Can you think of a way of getting the proper count without an array? I couldn't seem to come up with a working countif statement.

I tried this:
=COUNTIF(A1:A10,&quot;<>&quot;&quot;&quot
but no go... it might be getting hung up on the triple quotes.

I tried putting <>&quot;&quot; in another cell and referencing it but that didn't work either:
=COUNTIF(A1:A10,B1)
(where B1 contains <>&quot;&quot;)

 

[xlbo]

Bit of an odd one this - the last method works for me

In a new worksheet, in A1, enter
=IF(B1=&quot;&quot;,&quot;&quot;,B1)
and copy down to A10

In a11, enter
=countif(A1:A10,A10)

You will get a count of 10

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[TonyJollans]

Hi krinid, Geoff,

You guys have lost me a bit in this thread

Geoff's answer about formulae returning empty strings is sound. Another way to have an apparently blank cell is to have a single single-quote character in it. I don't know any way to get round these with a non-array formula.

But, whatever you're trying to do be careful with quotes in cell formulae (equally in cells referenced in formulae like B1 in your examples).

=COUNTIF(A1:A10,&quot;<>&quot;&quot;&quot counts cells which do not contain a single double-quote character
=COUNTIF(A1:A10,&quot;<>&quot should count cells which are not empty (same as COUNTA)

I've rambled a bit and provided no real answers so I'll go and look at another thread now

Enjoy,
Tony

 

[xlbo]

Tony:
=COUNTIF(A1:A10,&quot;<>&quot;&quot;&quot counts cells which do not contain a single double-quote character
Sorry but nuh-huh - doesn't work - should work theoretically but doesn;t in reality - thus the problem here

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[TonyJollans]

Works perfectly like that for me Geoff.

Just to confirm, I opened a new workbook, cut and pasted from your post to cell A11 - it showed &quot;10&quot;, then I entered &quot; in cell A1 and it (A11) changed to 9.

Is there some option somewhere which affects these things??

Enjoy,
Tony

 

[xlbo]

Hokey Cokey - the issue here is numbers of &quot;
Tony - that formula picks up &quot;
I was trying to pick up &quot;&quot;
to do that, we need some more &quot;&quot; :
=COUNTIF(A1:A10,&quot;<>&quot;&quot;&quot;&quot;&quot

Should've read your post more carefully - &quot;single double quote&quot; - I was looking for double double quotes

Now - the only problem left is that it still doesn't work for zero length strings - it'll work if you physically enter &quot;&quot; into a cell but that ain't the same thing

Rgds, Geoff
Si hoc legere scis, nimis eruditionis habes
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[krinid]

Geoff, Tony,
Thanks for the suggestions. I couldn't get any of the COUNTIF stuff to work without, as Geoff mentioned, having to physically insert ' or &quot; or &quot;&quot; etc into the cells I want to be 'blank', which I'm trying to avoid. I was looking for a one-line (sorry, may not have mentioned the 1-line part earlier), non-array formula to calculate it.

It doesn't look like this 1-line, non-array solution exists, so I'll just use the array formula.

Thanks guys,
krinid

 

[plantj]

Don't give up that easy

Curiously, it seems that COUNTBLANK counts cells containing a single quote even though ISBLANK returns false for such cells.

Try this - it may give you the one liner you need...

=COUNTA(a1:a10)-COUNTIF(a1:a10,&quot;&quot;&quot;&quot-COUNTBLANK(a1:a10)

 

[krinid]

plantj,
Thanks for the reply. This one liner works IF there are no 'completely blank cells', as opposed to cells containing empty strings.

This would work except that it's possible that someone may hit the DELETE key over a cell, which causes it to NOT be counted be COUNTA but still counted and hence subtracted by COUNTBLANK. (ie: COUNTA is reduced by 1, but COUNTBLANK still subtracts the same amount).

I don't think COUNTA can be used to find a reliable solution since it counts 'completely blank cells' and cells containing blanks differently.

Hmm... is there a way to count the # of rows? COUNTBLANK seems to always return the proper # of blank cells ('completely blank' or empty string containing cells being treated equal).

Thus:
=#rowsInRange) - COUNTBLANK(range)
seems to be able to offer the proper result; but I don't know how to simply count the number of rows (blank cells included).

 

[xlbo]

plantj - very nice - have a star. Must remember not to dismiss the obvious until I've actually tried it ;-)

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[krinid]

plantj,
Found it... indeed it is simple... your post put me on the right track:

=ROWS(A1:A10)-COUNTBLANK(A1:A10)

This handles both 'BLANK' and cells containing empty strings!

thanks to plantj, Geoff & Tony

 

[plantj]

Good thinking. Would this do?
=ROWS(range)-COUNTBLANK(range)

Or for good measure - taking out the cells with a single double quote...

=ROWS(range)-COUNTBLANK(range)-COUNTIF(range,&quot;&quot;&quot;&quot

 

[krinid]

After all the conversation gone on here, I can't help but wonder if there is any (logically sound) reason that COUNTA counts blank cells differently than COUNTBLANK.

 

[xlbo]

M$ development team A worked on countblank
M$ development team B worked on counta
?????????
To be fair, it is a pretty thin line of difference between a blank cell and one that contains a zero length string

Rgds, Geoff
It's Super Happy Campo Funtime!
Want the best answers to your questions ? - then read me baby one more time - faq222-2244

 

[krinid]

Geoff,
You're probably right (team A, B)!

I doublechecked the following point since I was under the impression the terminology 'blank cell' was thrown around loosely in the documentation with inconsistent meaning--but I was wrong: they specifically state in the documentation that both COUNTA and COUNTBLANK count cells containing empty strings! Thus this is becomes a 'feature', doesn't it?

Furthermore, they've provided a rich array of functions to be able to calculate what is actually desired (ROWS(...)-COUNTBLANK(...), array method, etc).