# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  > [SOLVED] How do you ignore hidden rows in a countif() function

## Scott buckwalter

I'd like to count the number of cells with the value "Open" that are not
hidden.
1) =COUNTIF(L:L,"Open")
This does not ignore hidden rows
2) =SUBTOTAL(3,L:L)
This ignores hidden rows but counts everything

What I like is a way to combine these two functions:
1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum to
be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this returns
a #VALUE error.

2) Is there an ishidden() function?  I could do this:
{=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0),0))}
But the ishidden() function does not exist.

3) I tried replacing the ISHIDDEN() with a CELL() function.  This gets me
closer, CELL("width") return 0 if the column is hidden, but not if the row is
hidden, I'd need to use CELL("height"). The end result:
{=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100)>0,1,0),0))}
Does not work since CELL("height") does not work.

Thanks for your help,
Scott

----------


## Domenic

Try...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1
00="Open"))

Hope this helps!

In article <057F667D-C889-4265-B810-03D823913789@microsoft.com>,
"Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote:

> I'd like to count the number of cells with the value "Open" that are not
> hidden.
> 1) =COUNTIF(L:L,"Open")
> This does not ignore hidden rows
> 2) =SUBTOTAL(3,L:L)
> This ignores hidden rows but counts everything
>
> What I like is a way to combine these two functions:
> 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum to
> be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this returns
> a #VALUE error.
>
> 2) Is there an ishidden() function?  I could do this:
> {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0),0))}
> But the ishidden() function does not exist.
>
> 3) I tried replacing the ISHIDDEN() with a CELL() function.  This gets me
> closer, CELL("width") return 0 if the column is hidden, but not if the row is
> hidden, I'd need to use CELL("height"). The end result:
> {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100)>0,1,0),0))}
> Does not work since CELL("height") does not work.
>
> Thanks for your help,
> Scott

----------


## Scott buckwalter

Thanks for the help.  I cannot get this to work.  It always returns 0.  Do I
need to tweek it a little?
Scott

"Domenic" wrote:

> Try...
>
> =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="Open"))
>
> Hope this helps!
>
> In article <057F667D-C889-4265-B810-03D823913789@microsoft.com>,
>  "Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote:
>
> > I'd like to count the number of cells with the value "Open" that are not
> > hidden.
> > 1) =COUNTIF(L:L,"Open")
> > This does not ignore hidden rows
> > 2) =SUBTOTAL(3,L:L)
> > This ignores hidden rows but counts everything
> >
> > What I like is a way to combine these two functions:
> > 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum to
> > be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this returns
> > a #VALUE error.
> >
> > 2) Is there an ishidden() function?  I could do this:
> > {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0),0))}
> > But the ishidden() function does not exist.
> >
> > 3) I tried replacing the ISHIDDEN() with a CELL() function.  This gets me
> > closer, CELL("width") return 0 if the column is hidden, but not if the row is
> > hidden, I'd need to use CELL("height"). The end result:
> > {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100)>0,1,0),0))}
> > Does not work since CELL("height") does not work.
> >
> > Thanks for your help,
> > Scott
>

----------


## Bob Phillips

Scott,

It works fine as it is, as long as the values 'Open' are in L2:L100. You
might need to extend the range.

--
HTH

Bob Phillips

"Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote in
message news:6E2B60A6-1D2B-404A-8C33-0D40330700D6@microsoft.com...
> Thanks for the help.  I cannot get this to work.  It always returns 0.  Do
I
> need to tweek it a little?
> Scott
>
> "Domenic" wrote:
>
> > Try...
> >
> >
=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="O
pen"))
> >
> > Hope this helps!
> >
> > In article <057F667D-C889-4265-B810-03D823913789@microsoft.com>,
> >  "Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote:
> >
> > > I'd like to count the number of cells with the value "Open" that are
not
> > > hidden.
> > > 1) =COUNTIF(L:L,"Open")
> > > This does not ignore hidden rows
> > > 2) =SUBTOTAL(3,L:L)
> > > This ignores hidden rows but counts everything
> > >
> > > What I like is a way to combine these two functions:
> > > 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the
sum to
> > > be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this
returns
> > > a #VALUE error.
> > >
> > > 2) Is there an ishidden() function?  I could do this:
> > > {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0),0))}
> > > But the ishidden() function does not exist.
> > >
> > > 3) I tried replacing the ISHIDDEN() with a CELL() function.  This gets
me
> > > closer, CELL("width") return 0 if the column is hidden, but not if the
row is
> > > hidden, I'd need to use CELL("height"). The end result:
> > > {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100)>0,1,0),0))}
> > > Does not work since CELL("height") does not work.
> > >
> > > Thanks for your help,
> > > Scott
> >

----------


## Domenic

Make sure that the second argument is preceded by a double negative
'--'...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1
00="Open"))

While I included the double negative in my original formula, for some
reason it's missing in the one quoted in your message.

Hope this helps!

In article <6E2B60A6-1D2B-404A-8C33-0D40330700D6@microsoft.com>,
"Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote:

> Thanks for the help.  I cannot get this to work.  It always returns 0.  Do I
> need to tweek it a little?
> Scott
>
> "Domenic" wrote:
>
> > Try...
> >
> > =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="O
> > pen"))
> >
> > Hope this helps!
> >
> > In article <057F667D-C889-4265-B810-03D823913789@microsoft.com>,
> >  "Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote:
> >
> > > I'd like to count the number of cells with the value "Open" that are not
> > > hidden.
> > > 1) =COUNTIF(L:L,"Open")
> > > This does not ignore hidden rows
> > > 2) =SUBTOTAL(3,L:L)
> > > This ignores hidden rows but counts everything
> > >
> > > What I like is a way to combine these two functions:
> > > 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum
> > > to
> > > be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this
> > > returns
> > > a #VALUE error.
> > >
> > > 2) Is there an ishidden() function?  I could do this:
> > > {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0),0))}
> > > But the ishidden() function does not exist.
> > >
> > > 3) I tried replacing the ISHIDDEN() with a CELL() function.  This gets me
> > > closer, CELL("width") return 0 if the column is hidden, but not if the
> > > row is
> > > hidden, I'd need to use CELL("height"). The end result:
> > > {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100)>0,1,0),0))}
> > > Does not work since CELL("height") does not work.
> > >
> > > Thanks for your help,
> > > Scott
> >

----------


## Scott buckwalter

Thanks! It works! I'm interested in Why it works.  Is there an explanation
somewhere?  I understand the functions being used (mostly), but I donât see
how putting them together in this way makes this work.  Thanks.

"Domenic" wrote:

> Make sure that the second argument is preceded by a double negative
> '--'...
>
> =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1
> 00="Open"))
>
> While I included the double negative in my original formula, for some
> reason it's missing in the one quoted in your message.
>
> Hope this helps!
>
> In article <6E2B60A6-1D2B-404A-8C33-0D40330700D6@microsoft.com>,
>  "Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote:
>
> > Thanks for the help.  I cannot get this to work.  It always returns 0.  Do I
> > need to tweek it a little?
> > Scott
> >
> > "Domenic" wrote:
> >
> > > Try...
> > >
> > > =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="O
> > > pen"))
> > >
> > > Hope this helps!
> > >
> > > In article <057F667D-C889-4265-B810-03D823913789@microsoft.com>,
> > >  "Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote:
> > >
> > > > I'd like to count the number of cells with the value "Open" that are not
> > > > hidden.
> > > > 1) =COUNTIF(L:L,"Open")
> > > > This does not ignore hidden rows
> > > > 2) =SUBTOTAL(3,L:L)
> > > > This ignores hidden rows but counts everything
> > > >
> > > > What I like is a way to combine these two functions:
> > > > 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying the sum
> > > > to
> > > > be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but this
> > > > returns
> > > > a #VALUE error.
> > > >
> > > > 2) Is there an ishidden() function?  I could do this:
> > > > {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0),0))}
> > > > But the ishidden() function does not exist.
> > > >
> > > > 3) I tried replacing the ISHIDDEN() with a CELL() function.  This gets me
> > > > closer, CELL("width") return 0 if the column is hidden, but not if the
> > > > row is
> > > > hidden, I'd need to use CELL("height"). The end result:
> > > > {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100)>0,1,0),0))}
> > > > Does not work since CELL("height") does not work.
> > > >
> > > > Thanks for your help,
> > > > Scott
> > >
>

----------


## Domenic

If we take a look at the following formula...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1
00="Open"))

....SUBTOTAL returns an array of 1's and 0's for the range of interest.
It returns 1 for all visible cells that are not empty, and returns 0 for
those that are hidden.  Therefore...

SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1))

....evaluates to something like...

1
1
0
0
0
1
0

....and so on for the remaining cells in the range.  The second
argument...

--(L2:L100="Open")

....evaluates to something like...

1
0
0
1
0
0
1

....and so on for the remaining cells in the range.  Then, SUMPRODUCT
multiplies the evaluations and sums the result.

Hope this helps!

In article <A989A824-D521-4B4B-96D4-962559982186@microsoft.com>,
"Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote:

> Thanks! It works! I'm interested in Why it works.  Is there an explanation
> somewhere?  I understand the functions being used (mostly), but I donât see
> how putting them together in this way makes this work.  Thanks.

----------


## Bob Phillips

Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
HTH

Bob Phillips

"Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote in
message news:A989A824-D521-4B4B-96D4-962559982186@microsoft.com...
> Thanks! It works! I'm interested in Why it works.  Is there an explanation
> somewhere?  I understand the functions being used (mostly), but I don't
see
> how putting them together in this way makes this work.  Thanks.
>
> "Domenic" wrote:
>
> > Make sure that the second argument is preceded by a double negative
> > '--'...
> >
> >
=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1
> > 00="Open"))
> >
> > While I included the double negative in my original formula, for some
> > reason it's missing in the one quoted in your message.
> >
> > Hope this helps!
> >
> > In article <6E2B60A6-1D2B-404A-8C33-0D40330700D6@microsoft.com>,
> >  "Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote:
> >
> > > Thanks for the help.  I cannot get this to work.  It always returns 0.
Do I
> > > need to tweek it a little?
> > > Scott
> > >
> > > "Domenic" wrote:
> > >
> > > > Try...
> > > >
> > > >
=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),(L2:L100="O
> > > > pen"))
> > > >
> > > > Hope this helps!
> > > >
> > > > In article <057F667D-C889-4265-B810-03D823913789@microsoft.com>,
> > > >  "Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com>
wrote:
> > > >
> > > > > I'd like to count the number of cells with the value "Open" that
are not
> > > > > hidden.
> > > > > 1) =COUNTIF(L:L,"Open")
> > > > > This does not ignore hidden rows
> > > > > 2) =SUBTOTAL(3,L:L)
> > > > > This ignores hidden rows but counts everything
> > > > >
> > > > > What I like is a way to combine these two functions:
> > > > > 1) Since {=SUM(IF(L1:L100="Open",1,0))} works, I tried modifying
the sum
> > > > > to
> > > > > be a subtotal, as such {=SUBTOTAL(9,IF(L1:L100="Open",1,0))} but
this
> > > > > returns
> > > > > a #VALUE error.
> > > > >
> > > > > 2) Is there an ishidden() function?  I could do this:
> > > > > {=SUM(IF(L1:L100="Open",IF(ISHIDDEN(L1:L100),1,0),0))}
> > > > > But the ishidden() function does not exist.
> > > > >
> > > > > 3) I tried replacing the ISHIDDEN() with a CELL() function.  This
gets me
> > > > > closer, CELL("width") return 0 if the column is hidden, but not if
the
> > > > > row is
> > > > > hidden, I'd need to use CELL("height"). The end result:
> > > > > {=SUM(IF(L1:L100="Open",IF(CELL("height",L1:L100)>0,1,0),0))}
> > > > > Does not work since CELL("height") does not work.
> > > > >
> > > > > Thanks for your help,
> > > > > Scott
> > > >
> >

----------


## Nick

I see where this counts the number of OPEN cells but what if you want to
count the open cells that meet a condition (>3.3)?
--
Thanks,
Nick


"Domenic" wrote:

> If we take a look at the following formula...
>
> =SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1
> 00="Open"))
>
> ....SUBTOTAL returns an array of 1's and 0's for the range of interest.
> It returns 1 for all visible cells that are not empty, and returns 0 for
> those that are hidden.  Therefore...
>
> SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1))
>
> ....evaluates to something like...
>
> 1
> 1
> 0
> 0
> 0
> 1
> 0
>
> ....and so on for the remaining cells in the range.  The second
> argument...
>
> --(L2:L100="Open")
>
> ....evaluates to something like...
>
> 1
> 0
> 0
> 1
> 0
> 0
> 1
>
> ....and so on for the remaining cells in the range.  Then, SUMPRODUCT
> multiplies the evaluations and sums the result.
>
> Hope this helps!
>
> In article <A989A824-D521-4B4B-96D4-962559982186@microsoft.com>,
>  "Scott buckwalter" <Scottbuckwalter@discussions.microsoft.com> wrote:
>
> > Thanks! It works! I'm interested in Why it works.  Is there an explanation
> > somewhere?  I understand the functions being used (mostly), but I donÃ¢â¬â¢t see
> > how putting them together in this way makes this work.  Thanks.
>

----------


## Domenic

In article <94DF672A-0AF6-4DD2-ADEB-A9ABFB21A9A1@microsoft.com>,
"Nick" <Nick@discussions.microsoft.com> wrote:

> I see where this counts the number of OPEN cells but what if you want to
> count the open cells that meet a condition (>3.3)?
> --
> Thanks,
> Nick

Assuming that Column M contains your numerical values...

=SUMPRODUCT(SUBTOTAL(3,OFFSET(L2:L100,ROW(L2:L100)-ROW(L2),0,1)),--(L2:L1
00="Open"),--(M2:M100>3.3))

Hope this helps!

----------

