COUNT BLANK FIELDS

[nikko50]

Hello friends.
Is there a formula I can write to count the number of blank cells in a column?? Thanks
Tracy

 

[fairwinds]

Hi,

Try:

=COUNTBLANK(A:A)

 

[nikko50]

Thanks so much for your help

 

[nikko50]

Hi Guys One last thing. Here is the formula I used...
=COUNTBLANK(E2:E165)
But how can I get this formula to change dynamically as new rows are added??

 

[fairwinds]

ADVERTISEMENT

If your column have numbers in it.

=COUNTBLANK(E2:INDEX(E:E,MATCH(9.99999999999999E+307,E:E)))

If it has text:

=COUNTBLANK(E2:INDEX(E:E,MATCH(REPT("z","255"),E:E)))

 

[nikko50]

The field can have numbers or letters
Tracy

 

[fairwinds]

ADVERTISEMENT

=COUNTBLANK(E2:INDEX(E1:E1000,MATCH(2,1/(E1:E1000<>""))))

But now the formula needs to be entered with Ctrl + shift + enter. You also must not have entire column references like E:E.



EDIT:

This one:
=COUNTBLANK(E2:INDEX(E1:E1000,LOOKUP(2,1/(E1:E1000<>""),ROW(E1:E1000))))

needs just normal enter.

 

[nikko50]

I can't get this to work. It's counting but it is wrong. I'm trying to count the blank cells in column E as long as there is an entry in column A. Column A is a date field where the user enters an order date. Column E is the shipped date field. I'm trying to count how many orders have not been shipped.
Tracy

 

[fairwinds]

So it is either one of:

=COUNTBLANK(E2:INDEX(E2:E20,MATCH(9.9999999999999E+307,A2:A20)))

or

=SUMPRODUCT(--(A2:A20<>""),--(E2:E20=""))

See which result is the one you want.

Book1
	A	B	C	D	E	F	G
1	Date				Entries
2	2002-10-10				xx	9	8
3	2002-10-11				xx
4	2002-10-12
5	2002-10-13
6	2002-10-14				xxx
7	2002-10-15
8
9	2002-10-17				x
10	2002-10-18
11	2002-10-19
12	2002-10-20				xxx
13	2002-10-21
14	2002-10-22
15	2002-10-23
16
17
18
Sheet7

 

[nikko50]

So let's say I add another order will this formula change to A2:A21 dynamically??