How do you ignore hidden rows in a countif() function
- From: "Scott buckwalter" <Scottbuckwalter@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 10 Aug 2005 07:28:01 -0700
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
.
- Follow-Ups:
- Prev by Date: repeat an action in different locations
- Next by Date: RE: Date/If function for past dues
- Previous by thread: repeat an action in different locations
- Next by thread: Re: How do you ignore hidden rows in a countif() function
- Index(es):
Relevant Pages
|
