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

**BNCOXUK** · 11-01-2012, 11:28 AM

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** · 11-01-2012, 11:39 AM

HI

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

**BNCOXUK** · 11-01-2012, 11:52 AM

Originally Posted by prashantha

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** · 11-01-2012, 12:26 PM

Anyone can offer a solution for post #3?

**Pepe Le Mokko** · 11-01-2012, 12:28 PM

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

**BNCOXUK** · 11-01-2012, 12:42 PM

Originally Posted by Pepe Le Mokko

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** · 11-01-2012, 12:59 PM

Try this formula

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

**BNCOXUK** · 11-01-2012, 01:43 PM

Originally Posted by daddylonglegs

Try this formula

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

This worked! Thanks a lot!

**Haseeb Avarakkan** · 11-02-2012, 03:22 PM

An alternate one,

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

**Colin Legg** · 11-02-2012, 04:11 PM

Another alternate one:

=COUNTIF(A:A,"=")

**Haseeb Avarakkan** · 11-02-2012, 04:19 PM

Originally Posted by Colin Legg

...=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** · 11-02-2012, 04:22 PM

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

**Colin Legg** · 11-02-2012, 04:25 PM

Is

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

any better?

**Haseeb Avarakkan** · 11-02-2012, 04:37 PM

Originally Posted by Colin Legg

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

Looks like working.

**Colin Legg** · 11-02-2012, 04:39 PM

Looks like working.

Cool.

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

With your formula I get 3.

**Haseeb Avarakkan** · 11-02-2012, 04:49 PM

Originally Posted by Colin Legg

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** · 11-02-2012, 04:50 PM

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

**daddylonglegs** · 11-02-2012, 05:55 PM

Originally Posted by Colin Legg

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

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

Originally Posted by Haseeb A

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

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

**Haseeb Avarakkan** · 11-04-2012, 06:32 PM

Hello DLL, thank you for that info.

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

LinkBack

Thread Tools

Rate This Thread

Display

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions