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

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
>
>

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
>
>

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
>
>

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
>
>

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" <akyurek@xs4all.nl> wrote in message
news:441f9d29$0$11066$e4fe514c@news.xs4all.nl...
> 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
>>

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" <biffinpitt@comcast.net> wrote in message
news:uTyJkQLTGHA.5168@TK2MSFTNGP10.phx.gbl...
> 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" <akyurek@xs4all.nl> wrote in message
> news:441f9d29$0$11066$e4fe514c@news.xs4all.nl...
>> 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
>>>
>

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" <biffinpitt@comcast.net> wrote in message
> news:uTyJkQLTGHA.5168@TK2MSFTNGP10.phx.gbl...
> > 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" <akyurek@xs4all.nl> wrote in message
> > news:441f9d29$0$11066$e4fe514c@news.xs4all.nl...
> >> 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

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" <GaryE.2511md@NoSpamPleaze.com> wrote in message
news:GaryE.2511md@NoSpamPleaze.com...
>
> 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" <biffinpitt@comcast.net> wrote in message
>> news:uTyJkQLTGHA.5168@TK2MSFTNGP10.phx.gbl...
>> > 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" <akyurek@xs4all.nl> wrote in message
>> > news:441f9d29$0$11066$e4fe514c@news.xs4all.nl...
>> >> 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
>

Biff wrote...
>There is a huge difference between an empty cell and a blank cell.

So true.

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

So wrong!

If X99 contained ="", does ISBLANK(X99) return TRUE or FALSE? If it
returns FALSE, why would you claim 'blank' means cells evaluating to
""?

In worksheet formulas, 'blank' has a precise meaning implied by the
ISBLANK function: the cell contains nothing. 'Empty' has no specified
meaning, so it's more naturally suited for use as meaning cells
containing zero length strings. Then there's the issue of cells
containing strings of nothing but one or more ASCII or HTML nonbreaking
spaces, which would also appear not to contain anything.

>Try this:
>
>Enter this FORMULA in A1: =""

And clear cell B1.

>Try these formulas:
>
>=ISBLANK(A1)

Indeed, do try this one. Then try

=ISBLANK(B1)

>=COUNTBLANK(A1)

This is where Microsoft screwed up. It'll return 1, but so will

=COUNTBLANK(B1)

>=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.

ISBLANK and COUNTA are the reliable indicators. LEN is just doing it's
job. Since Excel converts blank cells (cells containing nothing) to ""
in string contexts, LEN correctly returns 0 when passed a reference to
a blank cell. It's COUNTBLANK that's flawed.

>"GaryE" <GaryE.2511md@NoSpamPleaze.com> wrote in message
....
>>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.

Definitions are everything sometimes. What do you mean by 'empty'?

Also, just because C uses ASCII NUL as a string terminator, so zero
length strings in C would be represented in memory as a single NUL
byte, doesn't mean Excel or any other software or programming language
follows that approach. Indeed, Perl and gawk are two examples of
programming languages closely related to C that don't use ASCIIZ
strings. Strings can be implemented as structures or classes containing
at minimum a length property and a pointer to allocated memory used to
store the string. Such strings could store anything, including several
NUL bytes.

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

There are 4 formulas here, so in B1:E1?

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

That the 5th and 6th rows are the same as the 1st after clearing A5 and
A6 should be no surprise. Clearing A5 and A6 makes those cells blank,
so they're the same as A1.

As for your 4th row, is the cell formatted as Text? Or are you
forgetting to mention an initial single quote? The numeric constant 0
should produce the same results as the formula =0, so B4:E4 should be
the same as B2:E2 rather than B3:E3.

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.

> =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" <hrlngrv@aol.com> wrote in message
news:1142986195.956373.234780@g10g2000cwb.googlegroups.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.
>

>So wrong!

>If X99 contained ="", does ISBLANK(X99) return TRUE or FALSE? If it
>returns FALSE, why would you claim 'blank' means cells evaluating to
>""?

What do mean "so wrong"?

Where did I claim blank means cells evaluating to ""?

>>A blank cell can contain
>>a formula that returns "nothing"

That's why "nothing" is quoted!

Biff

"Harlan Grove" <hrlngrv@aol.com> wrote in message
news:1142985532.437120.208470@e56g2000cwe.googlegroups.com...
> Biff wrote...
>>There is a huge difference between an empty cell and a blank cell.
>
> So true.
>
>>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.
>
> So wrong!
>
> If X99 contained ="", does ISBLANK(X99) return TRUE or FALSE? If it
> returns FALSE, why would you claim 'blank' means cells evaluating to
> ""?
>
> In worksheet formulas, 'blank' has a precise meaning implied by the
> ISBLANK function: the cell contains nothing. 'Empty' has no specified
> meaning, so it's more naturally suited for use as meaning cells
> containing zero length strings. Then there's the issue of cells
> containing strings of nothing but one or more ASCII or HTML nonbreaking
> spaces, which would also appear not to contain anything.
>
>>Try this:
>>
>>Enter this FORMULA in A1: =""
>
> And clear cell B1.
>
>>Try these formulas:
>>
>>=ISBLANK(A1)
>
> Indeed, do try this one. Then try
>
> =ISBLANK(B1)
>
>>=COUNTBLANK(A1)
>
> This is where Microsoft screwed up. It'll return 1, but so will
>
> =COUNTBLANK(B1)
>
>>=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.
>
> ISBLANK and COUNTA are the reliable indicators. LEN is just doing it's
> job. Since Excel converts blank cells (cells containing nothing) to ""
> in string contexts, LEN correctly returns 0 when passed a reference to
> a blank cell. It's COUNTBLANK that's flawed.
>
>>"GaryE" <GaryE.2511md@NoSpamPleaze.com> wrote in message
> ...
>>>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.
>
> Definitions are everything sometimes. What do you mean by 'empty'?
>
> Also, just because C uses ASCII NUL as a string terminator, so zero
> length strings in C would be represented in memory as a single NUL
> byte, doesn't mean Excel or any other software or programming language
> follows that approach. Indeed, Perl and gawk are two examples of
> programming languages closely related to C that don't use ASCIIZ
> strings. Strings can be implemented as structures or classes containing
> at minimum a length property and a pointer to allocated memory used to
> store the string. Such strings could store anything, including several
> NUL bytes.
>
>>>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)
>
> There are 4 formulas here, so in B1:E1?
>
>>>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
> ...
>
> That the 5th and 6th rows are the same as the 1st after clearing A5 and
> A6 should be no surprise. Clearing A5 and A6 makes those cells blank,
> so they're the same as A1.
>
> As for your 4th row, is the cell formatted as Text? Or are you
> forgetting to mention an initial single quote? The numeric constant 0
> should produce the same results as the formula =0, so B4:E4 should be
> the same as B2:E2 rather than B3:E3.
>

you didn't read all of the posts : )

"Biff" wrote:

> > =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" <hrlngrv@aol.com> wrote in message
> news:1142986195.956373.234780@g10g2000cwb.googlegroups.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.
> >
>
>
>

Biff wrote...
....
>Where did I claim blank means cells evaluating to ""?
>
>>>A blank cell can contain
>>>a formula that returns "nothing"
>
>That's why "nothing" is quoted!
....

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

You may have been imprecise in your definition of 'blank', but your
definition of 'empty' is the definition of 'blank' (in the ISBLANK
sense), so by stating that 'blank' means something else you're wrong.

Simple linguistics would imply most people would draw a correspondence
between 'blank' the state and ISBLANK the function. If so, 'blank'
means the state in which cells contain nothing (no quotes). Some other
term, perhaps 'empty', could be used to refer to cells with no visible
result (I tend to use 'visually blank').

> you didn't read all of the posts : )

I did!

>> Never even thought of that.

I didn't think of it, you did!

I thought about selecting all the EMPTY cells and padding them with a
formula blank.

I just thought that there might be a formula that didn't "depend" on another
column of data being present.

Biff

"JMB" <JMB@discussions.microsoft.com> wrote in message
news:EAE60E9F-CFB0-4F98-8DE4-968663A38B53@microsoft.com...
> you didn't read all of the posts : )
>
> "Biff" wrote:
>
>> > =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" <hrlngrv@aol.com> wrote in message
>> news:1142986195.956373.234780@g10g2000cwb.googlegroups.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.
>> >
>>
>>
>>

> I did!
My apologies.

> I just thought that there might be a formula that didn't "depend" on another
> column of data being present.

Yeah, but darned if I can figure out how to identify an empty cell (while
weeding out those that are not visible due to a filter) using a more direct
approach. I was hoping someone else would figure it out. Maybe after the
blank cell versus empty cell issue is resolved? <g>



"Biff" wrote:

> > you didn't read all of the posts : )
>
> I did!
>
> >> Never even thought of that.
>
> I didn't think of it, you did!
>
> I thought about selecting all the EMPTY cells and padding them with a
> formula blank.
>
> I just thought that there might be a formula that didn't "depend" on another
> column of data being present.
>
> Biff
>
> "JMB" <JMB@discussions.microsoft.com> wrote in message
> news:EAE60E9F-CFB0-4F98-8DE4-968663A38B53@microsoft.com...
> > you didn't read all of the posts : )
> >
> > "Biff" wrote:
> >
> >> > =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" <hrlngrv@aol.com> wrote in message
> >> news:1142986195.956373.234780@g10g2000cwb.googlegroups.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.
> >> >
> >>
> >>
> >>
>
>
>

Maybe add a new column to the table and enter

=--(Cell("type", CellRef)="b")

and copy it down. Then use Subtotal(9, RangeRef) to get a count. Cell
seems to distinguish between truly empty cells and those that appear empty.
Interestingly, it seems Microsoft is also confused on the terminology.
According to help, the CELL function:

"Returns "b" for blank if the cell is empty"

Couldn't get it to work in an array formula, so it's still a two step process.

"Biff" wrote:

> > you didn't read all of the posts : )
>
> I did!
>
> >> Never even thought of that.
>
> I didn't think of it, you did!
>
> I thought about selecting all the EMPTY cells and padding them with a
> formula blank.
>
> I just thought that there might be a formula that didn't "depend" on another
> column of data being present.
>
> Biff
>
> "JMB" <JMB@discussions.microsoft.com> wrote in message
> news:EAE60E9F-CFB0-4F98-8DE4-968663A38B53@microsoft.com...
> > you didn't read all of the posts : )
> >
> > "Biff" wrote:
> >
> >> > =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" <hrlngrv@aol.com> wrote in message
> >> news:1142986195.956373.234780@g10g2000cwb.googlegroups.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.
> >> >
> >>
> >>
> >>
>
>
>

>Subtotal(9, RangeRef)

I tried to "leverage" that approach based on the single column attempts.

My logic was an empty cell would have a sum of zero, then just count the
number of zero sums.

It failed!

Subtotal isn't a very "flexible" function, sort of like the Rank function,
you can only do so much with it.

Biff

"JMB" <JMB@discussions.microsoft.com> wrote in message
news:55B70BC0-1FB6-4927-9835-1C257C16408C@microsoft.com...
> Maybe add a new column to the table and enter
>
> =--(Cell("type", CellRef)="b")
>
> and copy it down. Then use Subtotal(9, RangeRef) to get a count. Cell
> seems to distinguish between truly empty cells and those that appear
> empty.
> Interestingly, it seems Microsoft is also confused on the terminology.
> According to help, the CELL function:
>
> "Returns "b" for blank if the cell is empty"
>
> Couldn't get it to work in an array formula, so it's still a two step
> process.
>
> "Biff" wrote:
>
>> > you didn't read all of the posts : )
>>
>> I did!
>>
>> >> Never even thought of that.
>>
>> I didn't think of it, you did!
>>
>> I thought about selecting all the EMPTY cells and padding them with a
>> formula blank.
>>
>> I just thought that there might be a formula that didn't "depend" on
>> another
>> column of data being present.
>>
>> Biff
>>
>> "JMB" <JMB@discussions.microsoft.com> wrote in message
>> news:EAE60E9F-CFB0-4F98-8DE4-968663A38B53@microsoft.com...
>> > you didn't read all of the posts : )
>> >
>> > "Biff" wrote:
>> >
>> >> > =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" <hrlngrv@aol.com> wrote in message
>> >> news:1142986195.956373.234780@g10g2000cwb.googlegroups.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.
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

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" <biffinpitt@comcast.net> wrote in message
> news:uTyJkQLTGHA.5168@TK2MSFTNGP10.phx.gbl...
>
>>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" <akyurek@xs4all.nl> wrote in message
>>news:441f9d29$0$11066$e4fe514c@news.xs4all.nl...
>>
>>>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
>>>>
>>
>
>