Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
Hi, Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks? Thanks, Gos-C -- Gos-C ------------------------------------------------------------------------ Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518 View this thread: http://www.excelforum.com/showthread...hreadid=524428 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
No need to filter it first just use the following where "range" contains the range of cells with blank numbers =SUM(IF(ISBLANK(range),1,0)) just make sure you use control-shift-enter to enter the formula because this is an array formula. This checks to see if a cell is blank if it is it adds 1 to the total. HTH, Gary Gos-C Wrote: Hi, Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks? Thanks, Gos-C -- Gos-C ------------------------------------------------------------------------ Gos-C's Profile: http://www.***************/member.ph...o&userid=14518 View this thread: http://www.***************/showthrea...hreadid=524428 -- GaryE Posted from - http://www.officehelp.in |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
If you are not using the filter, what is wrong with using the countblank
function? "GaryE" wrote: No need to filter it first just use the following where "range" contains the range of cells with blank numbers =SUM(IF(ISBLANK(range),1,0)) just make sure you use control-shift-enter to enter the formula because this is an array formula. This checks to see if a cell is blank if it is it adds 1 to the total. HTH, Gary Gos-C Wrote: Hi, Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks? Thanks, Gos-C -- Gos-C ------------------------------------------------------------------------ Gos-C's Profile: http://www.***************/member.ph...o&userid=14518 View this thread: http://www.***************/showthrea...hreadid=524428 -- GaryE Posted from - http://www.officehelp.in |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
I couldn't find a direct way of doing that, but I think you may be able to
compute it: Assuming your entire table is in A2:B7 and Column A of data contains some sort of unique identifier (basically - it is a column that is guaranteed not to be blank so that we can get a count of the total number of visible cells after filtering) and Column B contains the data that may have blanks in it, I believe you can use =SUBTOTAL(3,A2:A7)-SUBTOTAL(3,B2:B7) It seems that SUBTOTAL(3,B2:B7) will return a count of all items in column B that are visible and NOT empty/blank. Therefore, subtracting this from the total number of visible cells in Column A would give you the number of empty cells in column B that are visible. "Gos-C" wrote: Hi, Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks? Thanks, Gos-C -- Gos-C ------------------------------------------------------------------------ Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518 View this thread: http://www.excelforum.com/showthread...hreadid=524428 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
Alternatively, if all you need is the number of blank cells in a specified
column, don't bother w/filtering out the blank cells (as Gary said) - use his formula or use COUNTBLANK function. "Gos-C" wrote: Hi, Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks? Thanks, Gos-C -- Gos-C ------------------------------------------------------------------------ Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518 View this thread: http://www.excelforum.com/showthread...hreadid=524428 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
Assuming that the secret column range is B5:B200...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B200)-ROW(B5)),,1)),--(B5:B200="")) Gos-C wrote: Hi, Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks? Thanks, Gos-C |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
That doesn't work. Result = 0, unfiltered or filtered. I tried that formula
(along with many other variations) earlier. Typo in the formula, one too many ")" ROW(B5)),,1)) ROW(B5),,1)) Biff "Aladin Akyurek" wrote in message ... Assuming that the secret column range is B5:B200... =SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B200)-ROW(B5)),,1)),--(B5:B200="")) Gos-C wrote: Hi, Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks? Thanks, Gos-C |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
Clarification:
That doesn't work......... If the cells are EMPTY, it does work if the cells have formula BLANKS. Don't know whether the OP meant "blank" or empty. How would you count EMPTY cells? I haven't been able to figure it out. Biff "Biff" wrote in message ... That doesn't work. Result = 0, unfiltered or filtered. I tried that formula (along with many other variations) earlier. Typo in the formula, one too many ")" ROW(B5)),,1)) ROW(B5),,1)) Biff "Aladin Akyurek" wrote in message ... Assuming that the secret column range is B5:B200... =SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B200)-ROW(B5)),,1)),--(B5:B200="")) Gos-C wrote: Hi, Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks? Thanks, Gos-C |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
This one really intrigues me. I am trying to figure out the difference between a blank cell and an empty cell. A cell that has not been touched in excel evaluates to zero. It will also evaluate to ISBLANK(); TRUE. If I enter random data in a cell press return and then delete that data the cell evaluates the same way. This tells me that there is no difference between a blank cell and an empty cell. FWIW if you use the following formula to evaluate a cell you get the #VALUE error. =if(A1=(char(0)),true.false) Char(0) is the ascii value for null (or blank if you prefer). You even get the #VALUE error if you place the following formula in cell A1 =char(0). So as far as I can tell there is no difference between an Empty cell and a blank cell. And Excel does not designate empty cells as ascii character 0. And Excel treats an empty cell the same way as a the number 0. To further expand on this I did the following. Starting on a new untouched worksheet. I put the following formulas in cells b1-d1 respectively and filled down for 6 cells. =IF(A1=0,1,0) =IF(ISNUMBER(A1),1,0) =IF(ISBLANK(A1),1,0) =IF(A1="0",1,0) I put the following in cells A1-A6 nothing =0 ="0" 0 entered a number and then deleted entered text and then deleted the results of the formulas in cells B-E are 1 0 1 0 1 1 0 0 0 0 0 1 0 0 0 1 1 0 1 0 1 0 1 0 Don't know if this helps anyone but it was an interesting academic excersize. Gary Biff Wrote: Clarification: That doesn't work......... If the cells are EMPTY, it does work if the cells have formula BLANKS. Don't know whether the OP meant "blank" or empty. How would you count EMPTY cells? I haven't been able to figure it out. Biff "Biff" wrote in message ... That doesn't work. Result = 0, unfiltered or filtered. I tried that formula (along with many other variations) earlier. Typo in the formula, one too many ")" ROW(B5)),,1)) ROW(B5),,1)) Biff "Aladin Akyurek" wrote in message ... Assuming that the secret column range is B5:B200... =SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B200)-ROW(B5)),,1)),--(B5:B200="")) Gos-C wrote: Hi, Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks? Thanks, Gos-C -- GaryE Posted from - http://www.officehelp.in |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
Thanks, everyone, for your responses. I am very busy at work right now so I will follow up later. Gos-C -- Gos-C ------------------------------------------------------------------------ Gos-C's Profile: http://www.excelforum.com/member.php...o&userid=14518 View this thread: http://www.excelforum.com/showthread...hreadid=524428 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
Hi!
There is a huge difference between an empty cell and a blank cell. The "problem" arises when people use the 2 terms interchangeably. An empty cell is one that contains nothing, nothing at all. A blank cell can contain a formula that returns "nothing" so the cell is not empty, it contains a formula but the cell appears to be empty. Try this: Enter this FORMULA in A1: ="" Try these formulas: =ISBLANK(A1) =COUNTBLANK(A1) =COUNTA(A1) =LEN(A1) 2 of those formulas might lead you to believe that cell A1 is empty and the other 2 "indicate" that something's there. Biff "GaryE" wrote in message ... This one really intrigues me. I am trying to figure out the difference between a blank cell and an empty cell. A cell that has not been touched in excel evaluates to zero. It will also evaluate to ISBLANK(); TRUE. If I enter random data in a cell press return and then delete that data the cell evaluates the same way. This tells me that there is no difference between a blank cell and an empty cell. FWIW if you use the following formula to evaluate a cell you get the #VALUE error. =if(A1=(char(0)),true.false) Char(0) is the ascii value for null (or blank if you prefer). You even get the #VALUE error if you place the following formula in cell A1 =char(0). So as far as I can tell there is no difference between an Empty cell and a blank cell. And Excel does not designate empty cells as ascii character 0. And Excel treats an empty cell the same way as a the number 0. To further expand on this I did the following. Starting on a new untouched worksheet. I put the following formulas in cells b1-d1 respectively and filled down for 6 cells. =IF(A1=0,1,0) =IF(ISNUMBER(A1),1,0) =IF(ISBLANK(A1),1,0) =IF(A1="0",1,0) I put the following in cells A1-A6 nothing =0 ="0" 0 entered a number and then deleted entered text and then deleted the results of the formulas in cells B-E are 1 0 1 0 1 1 0 0 0 0 0 1 0 0 0 1 1 0 1 0 1 0 1 0 Don't know if this helps anyone but it was an interesting academic excersize. Gary Biff Wrote: Clarification: That doesn't work......... If the cells are EMPTY, it does work if the cells have formula BLANKS. Don't know whether the OP meant "blank" or empty. How would you count EMPTY cells? I haven't been able to figure it out. Biff "Biff" wrote in message ... That doesn't work. Result = 0, unfiltered or filtered. I tried that formula (along with many other variations) earlier. Typo in the formula, one too many ")" ROW(B5)),,1)) ROW(B5),,1)) Biff "Aladin Akyurek" wrote in message ... Assuming that the secret column range is B5:B200... =SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B200)-ROW(B5)),,1)),--(B5:B200="")) Gos-C wrote: Hi, Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks? Thanks, Gos-C -- GaryE Posted from - http://www.officehelp.in |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
Biff wrote...
.... How would you count EMPTY cells? I haven't been able to figure it out. .... Presumably you mean cells containing nothing. If there were another column in the filtered table that always contained something, then =SUBTOTAL(3,OtherColumn)-SUBTOTAL(3,GivenColumn) would count the number of cells in GivenColumn that contain nothing. As for cells that contain things that evaluate to "", counting them requires udfs. |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
=SUBTOTAL(3,OtherColumn)-SUBTOTAL(3,GivenColumn)
Doh! Never even thought of that. As for cells that contain things that evaluate to "" Aladins formula seems to work for that. Lightly tested. Biff "Harlan Grove" wrote in message oups.com... Biff wrote... ... How would you count EMPTY cells? I haven't been able to figure it out. ... Presumably you mean cells containing nothing. If there were another column in the filtered table that always contained something, then =SUBTOTAL(3,OtherColumn)-SUBTOTAL(3,GivenColumn) would count the number of cells in GivenColumn that contain nothing. As for cells that contain things that evaluate to "", counting them requires udfs. |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Blanks in a Filtered Column
Two points...
1) The suggestion I made has a paren too many at the wrong place. 2) More important: The Subtotal bit should take not the range subject to counting, rather a range where the filter is applied. =SUMPRODUCT(SUBTOTAL(3,OFFSET(A5:A200,ROW(A5:A200)-ROW(A5),,1)),--(B5:B200="")) where A5:A200 is object of filtering and B5:B200 object of counting. This would yield a count of empty cells and formula-blanks. =SUMPRODUCT(SUBTOTAL(3,OFFSET(A5:A200,ROW(A5:A200)-ROW(A5),,1)),--ISBLANK(B5:B200=)) This would yield a count of empty cells only. ISBLANK ignores cells created with formulas like ="". Biff wrote: Clarification: That doesn't work......... If the cells are EMPTY, it does work if the cells have formula BLANKS. Don't know whether the OP meant "blank" or empty. How would you count EMPTY cells? I haven't been able to figure it out. Biff "Biff" wrote in message ... That doesn't work. Result = 0, unfiltered or filtered. I tried that formula (along with many other variations) earlier. Typo in the formula, one too many ")" ROW(B5)),,1)) ROW(B5),,1)) Biff "Aladin Akyurek" wrote in message . .. Assuming that the secret column range is B5:B200... =SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B2 00)-ROW(B5)),,1)),--(B5:B200="")) Gos-C wrote: Hi, Is there a function to count blanks in a filtered column? That is, if I filter a column to show the rows that are blank, can I use the subtotal function (or some other) to count the number of blanks? Thanks, Gos-C |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a column of numbers from a column with some blanks? | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Formula to lookup Multiple Column Text and then Count Result | Excel Worksheet Functions |