COUNT BLANK FIELDS

nikko50

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

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Hi,

Try:

=COUNTBLANK(A:A)

nikko50

Board Regular
Thanks so much for your help

nikko50

Board Regular
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

MrExcel MVP
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

Board Regular
The field can have numbers or letters
Tracy

fairwinds

MrExcel MVP
=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

Board Regular
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

MrExcel MVP
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
ABCDEFG
1DateEntries
22002-10-10xx98
32002-10-11xx
42002-10-12
52002-10-13
62002-10-14xxx
72002-10-15
8
92002-10-17x
102002-10-18
112002-10-19
122002-10-20xxx
132002-10-21
142002-10-22
152002-10-23
16
17
18
Sheet7

nikko50

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

Replies
7
Views
553
Replies
7
Views
305
Replies
5
Views
123
Replies
2
Views
214
Replies
5
Views
128

1,195,654
Messages
6,010,935
Members
441,575
Latest member
JOHNNY18031

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.

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

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