How to use COUNTIF to count the number of cells with null strings ""

[BNCOXUK]

I have a column and i want to count how many cells have null strings ""

I tried to do it like: =COUNTIF($A:$A,""), but not working.

Can you help me please?

[prashantha]

HI

NOTHING WRONG WITH YOUR FOMULA.
However I herewith attached sample. try it on your computer & let me know the result.

[BNCOXUK]

HI

NOTHING WRONG WITH YOUR FOMULA.
However I herewith attached sample. try it on your computer & let me know the result.

Is there anyway that I don't specify the row numbers and instead use the generic column name like $A:$A. Simply, the objective is to:

1) count the number of cells in a column which have null strings ""
2) use the generic column name $A:$A, without having to specify the row numbers like $A1:$A100.

[BNCOXUK]

Anyone can offer a solution for post #3?

[Pepe Le Mokko]

Have you opened post#2 example? Your answer is there !

[BNCOXUK]

Have you opened post#2 example? Your answer is there !

Hi, Pepe, the formula =COUNTIF($A:$A,"") is not very useful, as it also counts those null strings as well as all blank cells! I only want those cells with null strings "".

[daddylonglegs]

Try this formula

=COUNTBLANK(A:A)+COUNTA(A:A)-ROWS(A:A)

[BNCOXUK]

Try this formula

=COUNTBLANK(A:A)+COUNTA(A:A)-ROWS(A:A)

This worked! Thanks a lot!

[Haseeb Avarakkan]

An alternate one,

=SUM(COUNTIF(A:A,{"<>","?*","<9E300"})*{1,-1,-1})

[Colin Legg]

Another alternate one:

=COUNTIF(A:A,"=")

[Haseeb Avarakkan]

...=COUNTIF(A:A,"=")

Colin,

I don't think so this does work

A1: 1
A2: A
A3: =""

with your formula I got 1048573 (in Excel 2007). But it must be 1. Or am I miss something?

[Colin Legg]

You're right, should've tested it before posting! Thanks for picking it up.

[Colin Legg]

Is

=SUM(COUNTIF(A:A,{"=",""})*{-1,1})

any better?

[Haseeb Avarakkan]

...=SUM(COUNTIF(A:A,{"=",""})*{-1,1})...

Looks like working.

[Colin Legg]

Looks like working.

Cool.

A1: =NA()
A2: =NA()
A3: =NA()

With your formula I get 3.

[Haseeb Avarakkan]

Cool.

A1: =NA()
A2: =NA()
A3: =NA()

With your formula I get 3.

Yes Colin. I just found that error & I was coming to reply

@All, Sorry for the inconvenience.

[Colin Legg]

No inconvenience at all - you were a great help straightening up my dodgy attempt.

[daddylonglegs]

=SUM(COUNTIF(A:A,{"=",""})*{-1,1})

Hey, Colin! Yeah, I like that one......

=SUM(COUNTIF(A:A,{"<>","?*","<9E300"})*{1,-1,-1})

Hello Haseeb, this one will also count logical values like TRUE and FALSE

[Haseeb Avarakkan]

Hello DLL, thank you for that info.