Countbalnk Formula
 

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.

Countbalnk Formula
 

Maybe use the SUBTOTAL formula to COUNT the total number of filtered cells on
a contiguious column, and then subtract the COUNT of those cells in your
dated column which have been completed.

=SUBTOTAL(2,A:A)-SUBTOTAL(2,b:b)

hth
Vaya con Dios,
Chuck, CABGx3



"Craig" wrote:

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.

Countbalnk Formula
 

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 wrote:
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.

Countbalnk Formula
 

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


"Scott" wrote in message
ups.com...
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 wrote:
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.

Countbalnk Formula
 

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 wrote:
The problem is the columns contain Dates. I am thinking that maybe this is
not possible.


"Scott" wrote in message
ups.com...
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 wrote:
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.

Countbalnk Formula
 

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.



"Scott" wrote in message
oups.com...
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 wrote:
The problem is the columns contain Dates. I am thinking that maybe this
is
not possible.


"Scott" wrote in message
ups.com...
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 wrote:
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.