Countbalnk Formula

[Craig]

I am trying to use the Countblank formula in conjuction with the Auto Filter
Function. Let me try and explain. I have data listed in date order in
colum e; and in column F I have the date which I have tested the data.
ANyway not that that information matters. I want to be able to select a
date in column E using the auto filter feature and want to use the
CountBlank formula to count how many blank cells show up in column F. The
problem is, the CountBlank formula counts all the blanks in that column. So
I think what I need is something similar to the Subtotal function you use to
total columns hen using the sutofilter, only I need it to count the blank
cells.

Does anyone have any idea how I can do this?

Thanks for the help.

 

[Scott]

You could probably do the following:

=SUBTOTAL(3,E2:E100)-SUBTOTAL(3,F2:F100)

That is, count the number of rows by doing a COUNTA on column E (which
you seem to have implied always has data), then subtract the number
that have a value in column F, by doing a COUNTA on column F. The
difference is the blank cells.

Scott

 

[Craig]

The problem is the columns contain Dates. I am thinking that maybe this is
not possible.

 

[Scott]

Not to be rude... but you might want to test this, not just think about
it. If you have a Date in a cell (say A1), the formula COUNTA(A1) = 1.
Similarly, if you use SUBTOTAL(3, A1) you'll get 1 if cell A1 is not
excluded by your filter.

Of course, I may not know some of the particulars of your spreadsheet.


Scott

 

[Craig]

Thanks for the help Scott. Here is what I did and a little more
information. In column A I have a date (Date of Last Testing) and in Column
B I have the (Current Test Date). So what I wanted is to use autofilter and
select everything I tested on 6/30/06 and get a count on how many I have
left to test (Column B Blanks).

This is what I did for a solution. In may have not been the best but I
added a two helper columns with IF statement. If a1<>"",1,"" so this will
give me a 1 in column C for (Date Last Tested), this should always =1. IN
column D my IF statement was IF b1="",1,"". So this gives me a vaule on 1
if the cell is blank. then at the of the autofilter I can use the Subtotal
command and get a count of the blanks.