Closed Thread
Results 1 to 10 of 10

How do you ignore hidden rows in a countif() function

  1. #1
    Scott buckwalter
    Guest

    How do you ignore hidden rows in a countif() function

    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

  2. #2
    Domenic
    Guest

    Re: How do you ignore hidden rows in a countif() function

    Try...

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

    Hope this helps!

    In article <[email protected]>,
    "Scott buckwalter" <[email protected]> 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


  3. #3
    Scott buckwalter
    Guest

    Re: How do you ignore hidden rows in a countif() function

    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 <[email protected]>,
    > "Scott buckwalter" <[email protected]> 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

    >


  4. #4
    Bob Phillips
    Guest

    Re: How do you ignore hidden rows in a countif() function

    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" <[email protected]> wrote in
    message news:[email protected]...
    > 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 <[email protected]>,
    > > "Scott buckwalter" <[email protected]> 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

    > >




  5. #5
    Domenic
    Guest

    Re: How do you ignore hidden rows in a countif() function

    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 <[email protected]>,
    "Scott buckwalter" <[email protected]> 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 <[email protected]>,
    > > "Scott buckwalter" <[email protected]> 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

    > >


  6. #6
    Scott buckwalter
    Guest

    Re: How do you ignore hidden rows in a countif() function

    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 <[email protected]>,
    > "Scott buckwalter" <[email protected]> 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 <[email protected]>,
    > > > "Scott buckwalter" <[email protected]> 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
    > > >

    >


  7. #7
    Domenic
    Guest

    Re: How do you ignore hidden rows in a countif() function

    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 <[email protected]>,
    "Scott buckwalter" <[email protected]> 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.


  8. #8
    Bob Phillips
    Guest

    Re: How do you ignore hidden rows in a countif() function

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

    --
    HTH

    Bob Phillips

    "Scott buckwalter" <[email protected]> wrote in
    message news:[email protected]...
    > 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 <[email protected]>,
    > > "Scott buckwalter" <[email protected]> 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 <[email protected]>,
    > > > > "Scott buckwalter" <[email protected]>

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

    > >




  9. #9
    Nick
    Guest

    Re: How do you ignore hidden rows in a countif() function

    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 <[email protected]>,
    > "Scott buckwalter" <[email protected]> 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.

    >


  10. #10
    Domenic
    Guest

    Re: How do you ignore hidden rows in a countif() function

    In article <[email protected]>,
    "Nick" <[email protected]> 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!

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1