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