software

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: www.excelforum.com/member.php...oamp;userid=14518
View this thread: www.excelforum.com/showthread...hreadid=524428
No need to filter it first just use the following where quot;rangequot; 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:
gt; Hi,
gt;
gt; Is there a function to count blanks in a filtered column? That is, if
gt; I filter a column to show the rows that are blank, can I use the
gt; subtotal function (or some other) to count the number of blanks?
gt;
gt; Thanks,
gt; Gos-C
gt;
gt;
gt; --
gt; Gos-C
gt;
gt;
gt; ------------------------------------------------------------------------
gt; Gos-C's Profile:
gt; www.***************/member.ph...oamp;userid=14518
gt; View this thread:
gt; www.***************/showthrea...hreadid=524428--
GaryE
Posted from - www.officehelp.inIf you are not using the filter, what is wrong with using the countblank
function?quot;GaryEquot; wrote:

gt;
gt; No need to filter it first just use the following where quot;rangequot; contains
gt; the range of cells with blank numbers
gt;
gt; =SUM(IF(ISBLANK(range),1,0))
gt;
gt; just make sure you use control-shift-enter to enter the formula because
gt; this is an array formula.
gt;
gt; This checks to see if a cell is blank if it is it adds 1 to the total.
gt;
gt; HTH,
gt;
gt; Gary
gt;
gt; Gos-C Wrote:
gt; gt; Hi,
gt; gt;
gt; gt; Is there a function to count blanks in a filtered column? That is, if
gt; gt; I filter a column to show the rows that are blank, can I use the
gt; gt; subtotal function (or some other) to count the number of blanks?
gt; gt;
gt; gt; Thanks,
gt; gt; Gos-C
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Gos-C
gt; gt;
gt; gt;
gt; gt; ------------------------------------------------------------------------
gt; gt; Gos-C's Profile:
gt; gt; www.***************/member.ph...oamp;userid=14518
gt; gt; View this thread:
gt; gt; www.***************/showthrea...hreadid=524428
gt;
gt;
gt; --
gt; GaryE
gt; Posted from - www.officehelp.in
gt;
gt;

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.
quot;Gos-Cquot; wrote:

gt;
gt; Hi,
gt;
gt; Is there a function to count blanks in a filtered column? That is, if
gt; I filter a column to show the rows that are blank, can I use the
gt; subtotal function (or some other) to count the number of blanks?
gt;
gt; Thanks,
gt; Gos-C
gt;
gt;
gt; --
gt; Gos-C
gt;
gt;
gt; ------------------------------------------------------------------------
gt; Gos-C's Profile: www.excelforum.com/member.php...oamp;userid=14518
gt; View this thread: www.excelforum.com/showthread...hreadid=524428
gt;
gt;

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.quot;Gos-Cquot; wrote:

gt;
gt; Hi,
gt;
gt; Is there a function to count blanks in a filtered column? That is, if
gt; I filter a column to show the rows that are blank, can I use the
gt; subtotal function (or some other) to count the number of blanks?
gt;
gt; Thanks,
gt; Gos-C
gt;
gt;
gt; --
gt; Gos-C
gt;
gt;
gt; ------------------------------------------------------------------------
gt; Gos-C's Profile: www.excelforum.com/member.php...oamp;userid=14518
gt; View this thread: www.excelforum.com/showthread...hreadid=524428
gt;
gt;

Assuming that the secret column range is B5:B200...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B200)-ROW(B5)),,1)),--(B5:B200=quot;quot;))

Gos-C wrote:
gt; Hi,
gt;
gt; Is there a function to count blanks in a filtered column? That is, if
gt; I filter a column to show the rows that are blank, can I use the
gt; subtotal function (or some other) to count the number of blanks?
gt;
gt; Thanks,
gt; Gos-C
gt;
gt;

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 quot;)quot;

ROW(B5)),,1))

ROW(B5),,1))

Biff

quot;Aladin Akyurekquot; gt; wrote in message
...
gt; Assuming that the secret column range is B5:B200...
gt;
gt; =SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B200)-ROW(B5)),,1)),--(B5:B200=quot;quot;))
gt;
gt; Gos-C wrote:
gt;gt; Hi,
gt;gt;
gt;gt; Is there a function to count blanks in a filtered column? That is, if
gt;gt; I filter a column to show the rows that are blank, can I use the
gt;gt; subtotal function (or some other) to count the number of blanks?
gt;gt;
gt;gt; Thanks,
gt;gt; Gos-C
gt;gt;Clarification:

gt; 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 quot;blankquot; or empty.

How would you count EMPTY cells? I haven't been able to figure it out.

Biff

quot;Biffquot; gt; wrote in message
...
gt; That doesn't work. Result = 0, unfiltered or filtered. I tried that
gt; formula (along with many other variations) earlier.
gt;
gt; Typo in the formula, one too many quot;)quot;
gt;
gt; ROW(B5)),,1))
gt;
gt; ROW(B5),,1))
gt;
gt; Biff
gt;
gt; quot;Aladin Akyurekquot; gt; wrote in message
gt; ...
gt;gt; Assuming that the secret column range is B5:B200...
gt;gt;
gt;gt; =SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B200)-ROW(B5)),,1)),--(B5:B200=quot;quot;))
gt;gt;
gt;gt; Gos-C wrote:
gt;gt;gt; Hi,
gt;gt;gt;
gt;gt;gt; Is there a function to count blanks in a filtered column? That is, if
gt;gt;gt; I filter a column to show the rows that are blank, can I use the
gt;gt;gt; subtotal function (or some other) to count the number of blanks?
gt;gt;gt;
gt;gt;gt; Thanks,
gt;gt;gt; Gos-C
gt;gt;gt;
gt;

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=quot;0quot;,1,0)

I put the following in cells A1-A6
nothing
=0
=quot;0quot;
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:
gt; Clarification:
gt;
gt; gt; That doesn't work.........
gt;
gt; If the cells are EMPTY, it does work if the cells have formula BLANKS.
gt;
gt; Don't know whether the OP meant quot;blankquot; or empty.
gt;
gt; How would you count EMPTY cells? I haven't been able to figure it out.
gt;
gt; Biff
gt;
gt; quot;Biffquot; gt; wrote in message
gt; ...
gt; gt; That doesn't work. Result = 0, unfiltered or filtered. I tried that
gt; gt; formula (along with many other variations) earlier.
gt; gt;
gt; gt; Typo in the formula, one too many quot;)quot;
gt; gt;
gt; gt; ROW(B5)),,1))
gt; gt;
gt; gt; ROW(B5),,1))
gt; gt;
gt; gt; Biff
gt; gt;
gt; gt; quot;Aladin Akyurekquot; gt; wrote in message
gt; gt; ...
gt; gt;gt; Assuming that the secret column range is B5:B200...
gt; gt;gt;
gt; gt;gt;
gt; =SUMPRODUCT(SUBTOTAL(3,OFFSET(B5:B200,ROW(B5:B200)-ROW(B5)),,1)),--(B5:B200=quot;quot;))
gt; gt;gt;
gt; gt;gt; Gos-C wrote:
gt; gt;gt;gt; Hi,
gt; gt;gt;gt;
gt; gt;gt;gt; Is there a function to count blanks in a filtered column? That is,
gt; if
gt; gt;gt;gt; I filter a column to show the rows that are blank, can I use the
gt; gt;gt;gt; subtotal function (or some other) to count the number of blanks?
gt; gt;gt;gt;
gt; gt;gt;gt; Thanks,
gt; gt;gt;gt; Gos-C
gt; gt;gt;gt;
gt; gt;--
GaryE
Posted from - www.officehelp.in
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: www.excelforum.com/member.php...oamp;userid=14518
View this thread: www.excelforum.com/showthread...hreadid=524428