# A way around counting blanks in COUNTA?

#### Klevins11

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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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<>""))

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.

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.

COUNTA does not count empty strings "".
Yes it does, at least in XL2010 anyway.

Yes it does, at least in XL2010 anyway.
It counts zero length strings (blanks)?
I have Excel 2007 and it does not count those.

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

Excel Workbook
AB
1 3
2Text
312
4
5
6
7
8
9
10
Sheet1

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.

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.

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).

Replies
7
Views
299
Replies
1
Views
337
Replies
5
Views
426
Replies
10
Views
228
Replies
8
Views
111

Threads
1,203,072
Messages
6,053,377
Members
444,660
Latest member
Mingalsbe

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back