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