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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

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

Hope this helps!
 
Thanks for the help. I cannot get this to work. It always returns 0. Do I
need to tweek it a little?
Scott
 
Scott,

It works fine as it is, as long as the values 'Open' are in L2:L100. You
might need to extend the range.
 
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!
 
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.
 
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!
 
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)?
 
Nick said:
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)?
Click to expand...

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!
 
Back
Top