COUNTIF and AVERAGE only cells in unhidden rows
 

I'm using COUNTIF and AVERAGE. How do I count or average cells of only
unhidded rows

COUNTIF and AVERAGE only cells in unhidden rows
 

Hi,

If the rows are hidden as the result of a filtered list then you can use
SUBTOTAL.

=SUBTOTAL(1,A1:A10) for average
=SUBTOTAL(2,A1:A10) for count
=SUBTOTAL(3,A1:A10) for counta

not sure about the countif though!

HTH
Jean-Guy

"dford" wrote:

I'm using COUNTIF and AVERAGE. How do I count or average cells of only
unhidded rows

COUNTIF and AVERAGE only cells in unhidden rows
 

This is what I'm trying to do.
=COUNTIF($H$2:$H$599,"=25")-COUNTIF($H$2:$H$599,"30")

However, it counts cells in hidded rows. I want to count cells in only
unhidded rows. The rowa are hidden as a result of a filtered list.

"pinmaster" wrote:

Hi,

If the rows are hidden as the result of a filtered list then you can use
SUBTOTAL.

=SUBTOTAL(1,A1:A10) for average
=SUBTOTAL(2,A1:A10) for count
=SUBTOTAL(3,A1:A10) for counta

not sure about the countif though!

HTH
Jean-Guy

"dford" wrote:

I'm using COUNTIF and AVERAGE. How do I count or average cells of only
unhidded rows

COUNTIF and AVERAGE only cells in unhidden rows
 

Hi,

Not sure if this will help but you can use a custom filter in column H to
show only values between 25 and 30 then use =SUBTOTAL(2,H2:H599) to get a
count.

HTH
Jean-Guy

"dford" wrote:

This is what I'm trying to do.
=COUNTIF($H$2:$H$599,"=25")-COUNTIF($H$2:$H$599,"30")

However, it counts cells in hidded rows. I want to count cells in only
unhidded rows. The rowa are hidden as a result of a filtered list.

"pinmaster" wrote:

Hi,

If the rows are hidden as the result of a filtered list then you can use
SUBTOTAL.

=SUBTOTAL(1,A1:A10) for average
=SUBTOTAL(2,A1:A10) for count
=SUBTOTAL(3,A1:A10) for counta

not sure about the countif though!

HTH
Jean-Guy

"dford" wrote:

I'm using COUNTIF and AVERAGE. How do I count or average cells of only
unhidded rows

COUNTIF and AVERAGE only cells in unhidden rows
 

If I use a cuatom filter like you suggest, it will still show values in
hidden rows. I need to show values in only unhidden rows.

"pinmaster" wrote:

Hi,

Not sure if this will help but you can use a custom filter in column H to
show only values between 25 and 30 then use =SUBTOTAL(2,H2:H599) to get a
count.

HTH
Jean-Guy

"dford" wrote:

This is what I'm trying to do.
=COUNTIF($H$2:$H$599,"=25")-COUNTIF($H$2:$H$599,"30")

However, it counts cells in hidded rows. I want to count cells in only
unhidded rows. The rowa are hidden as a result of a filtered list.

"pinmaster" wrote:

Hi,

If the rows are hidden as the result of a filtered list then you can use
SUBTOTAL.

=SUBTOTAL(1,A1:A10) for average
=SUBTOTAL(2,A1:A10) for count
=SUBTOTAL(3,A1:A10) for counta

not sure about the countif though!

HTH
Jean-Guy

"dford" wrote:

I'm using COUNTIF and AVERAGE. How do I count or average cells of only
unhidded rows

COUNTIF and AVERAGE only cells in unhidden rows
 

Not if you use a subtotal function with a 2 as the first argument. It will
only count the visible cells (in filtered list). But I do get your
delima....the formula will only work when you use a custom filter and as long
as the custom filter is active. I'm sure there's a solution out there, maybe
a UDF but it's not something "I" can help you with. So hopefully some of the
MVP's out there have a solution for you soon.

HTH
Jean-Guy

"dford" wrote:

If I use a cuatom filter like you suggest, it will still show values in
hidden rows. I need to show values in only unhidden rows.

"pinmaster" wrote:

Hi,

Not sure if this will help but you can use a custom filter in column H to
show only values between 25 and 30 then use =SUBTOTAL(2,H2:H599) to get a
count.

HTH
Jean-Guy

"dford" wrote:

This is what I'm trying to do.
=COUNTIF($H$2:$H$599,"=25")-COUNTIF($H$2:$H$599,"30")

However, it counts cells in hidded rows. I want to count cells in only
unhidded rows. The rowa are hidden as a result of a filtered list.

"pinmaster" wrote:

Hi,

If the rows are hidden as the result of a filtered list then you can use
SUBTOTAL.

=SUBTOTAL(1,A1:A10) for average
=SUBTOTAL(2,A1:A10) for count
=SUBTOTAL(3,A1:A10) for counta

not sure about the countif though!

HTH
Jean-Guy

"dford" wrote:

I'm using COUNTIF and AVERAGE. How do I count or average cells of only
unhidded rows

COUNTIF and AVERAGE only cells in unhidden rows
 

Try adding a helper column. For this example enter in I2 the formula:

=SUBTOTAL(9,H2)

and fill down, then use column I for your countif formula.

On Feb 9, 5:36 am, dford wrote:
This is what I'm trying to do.
=COUNTIF($H$2:$H$599,"=25")-COUNTIF($H$2:$H$599,"30")

However, it counts cells in hidded rows. I want to count cells in only
unhidded rows. The rowa are hidden as a result of a filtered list.

"pinmaster" wrote:
Hi,

If the rows are hidden as the result of a filtered list then you can use
SUBTOTAL.

=SUBTOTAL(1,A1:A10) for average
=SUBTOTAL(2,A1:A10) for count
=SUBTOTAL(3,A1:A10) for counta

not sure about the countif though!

HTH
Jean-Guy

"dford" wrote:

I'm using COUNTIF and AVERAGE. How do I count or average cells of only
unhidded rows

COUNTIF and AVERAGE only cells in unhidden rows
 

In article ,
dford wrote:

This is what I'm trying to do.
=COUNTIF($H$2:$H$599,"=25")-COUNTIF($H$2:$H$599,"30")

However, it counts cells in hidded rows. I want to count cells in only
unhidded rows. The rowa are hidden as a result of a filtered list.

Try...

=SUMPRODUCT(SUBTOTAL(2,OFFSET($H$2:$H$599,ROW($H$2 :$H$599)-ROW($H$2),0,1)
),--($H$2:$H$599=25),--($H$2:$H$599<=30))

Hope this helps!