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
Bookmarks