Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I'm using COUNTIF and AVERAGE. How do I count or average cells of only
unhidded rows |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
COUNTIF in between rows | Excel Worksheet Functions | |||
Take an average of a range of cells but exlude blanks from that av | Excel Discussion (Misc queries) | |||
Countif cell greater than average | Excel Worksheet Functions | |||
Average Non-Continuous Cells Without Zero's | Excel Worksheet Functions | |||
Average non continguous cells, excluding zero's | Excel Worksheet Functions |