Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |