COUNTIF and AVERAGE only cells in unhidden rows

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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.

 

[Guest]

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

 

[Lori]

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.

 

[Domenic]

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!