D
dford
I'm using COUNTIF and AVERAGE. How do I count or average cells of only
unhidded rows
unhidded rows
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
P
pinmaster
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
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
D
dford
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.
=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.
P
pinmaster
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
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
D
dford
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.
hidden rows. I need to show values in only unhidden rows.
P
pinmaster
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
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
L
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.
=SUBTOTAL(9,H2)
and fill down, then use column I for your countif formula.
D
Domenic
dford said: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!