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

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!

In article ,
"Scott buckwalter" 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

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="Open"))

Hope this helps!

In article ,
"Scott buckwalter" 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

Scott,

It works fine as it is, as long as the values 'Open' are in L2:L100. You
might need to extend the range.

--
HTH

Bob Phillips

"Scott buckwalter" wrote in
message ...
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 ,
"Scott buckwalter" 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

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 ,
"Scott buckwalter" 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 ,
"Scott buckwalter" 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

Thanks! It works! I'm interested in Why it works. Is there an explanation
somewhere? I understand the functions being used (mostly), but I dont 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 ,
"Scott buckwalter" 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 ,
"Scott buckwalter" 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

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!

In article ,
"Scott buckwalter" wrote:

Thanks! It works! I'm interested in Why it works. Is there an explanation
somewhere? I understand the functions being used (mostly), but I dont see
how putting them together in this way makes this work. Thanks.

Take a look at http://www.xldynamic.com/source/xld.SUMPRODUCT.html

--
HTH

Bob Phillips

"Scott buckwalter" wrote in
message ...
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 ,
"Scott buckwalter" 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 ,
"Scott buckwalter"
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

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)?
--
Thanks,
Nick


"Domenic" wrote:

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!

In article ,
"Scott buckwalter" wrote:

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.

In article ,
"Nick" wrote:

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)?
--
Thanks,
Nick

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:M1003.3))

Hope this helps!