A way around counting blanks in COUNTA?

[Klevins11]

Is there any way to stop COUNTA from counting "" as text? Or any way around this?

 

[Jonmo1]

Generally there's no way around it, for that funciton. It's just what that function does.
You can use other formulas to count Non Blanks, like
=SUMPRODUCT(--(A1:A10<>""))

 

[Joe4]

COUNTA does not count empty strings "". My guess is that you do not have an empty string, but maybe a single space, " "?

Easy way to check is to try to use the LEN function on one of these cells (i.e. =LEN(A1)).
If it returns anything other than zero, you do not have an empty string/blank.

 

[Jonmo1]

This is something I've always found strange.
COUNTA will consider ="" as NOT Blank
However
COUNTBLANK will actually consider ="" as Blank.

I don't really care either way, if Excel says it's not blank, fine.
Or if Excel says it IS blank, fine.
Either way, But both functions should consider it the same..

Anyway, off the soapbox.

With that in mind, since countblank actually DOES consider ="" as blank, then you can do this as well
=10-COUNTBLANK(A1:A10)

The 10 should be equal to the number of rows considered in the countblank.

 

[Jonmo1]

COUNTA does not count empty strings "".

Yes it does, at least in XL2010 anyway.

 

[Joe4]

Yes it does, at least in XL2010 anyway.

It counts zero length strings (blanks)?
I have Excel 2007 and it does not count those.

 

[Jonmo1]

Yep, COUNTA counts formula blanks (zero length strings.. ="")

Excel Workbook
	A	B
1		3
2	Text
3	12
4
5
6
7
8
9
10
Sheet1

 

[Aladin Akyurek]

Is there any way to stop COUNTA from counting "" as text? Or any way around this?

If the reference of interest consists solely of text, including the formula blanks, you can invoke:

=COUNTIFS(A:A,"?*")

which will exclude the blanks from the count.

Othwerwise, you need an array-processing approach, like one with SumProduct which is already suggested.

 

[Joe4]

Yep, COUNTA counts formula blanks (zero length strings.. ="")

I think we are talking apples and oranges here. I mean cells that have absolutely nothing in them (no formulas or anything - that is what I consider blanks).
If they are talking about the results of a formula, that would be different, as you have pointed out.

 

[Aladin Akyurek]

I think we are talking apples and oranges here. I mean cells that have absolutely nothing in them (no formulas or anything - that is what I consider blanks).
If they are talking about the results of a formula, that would be different, as you have pointed out.

You see more often than not a blank refer to (1) an empty/unused cell as well as to a cell housing (2) a formula blank (like one that you would get with: =""). The latter is perhaps partly due the differeing behavior of COUNTBLANK and COUNTA regarding (2).