How do I complete a countif that dosen't include hiden cells

T

Telecom

I have a column of various text answers and I want to count the number of
times they appear, but I also have criteria which hides rows of information
and I want the count when these lines are hiden.
 
B

Billy Liddel

Telecom said:
I have a column of various text answers and I want to count the number of
times they appear, but I also have criteria which hides rows of information
and I want the count when these lines are hiden.
Click to expand...

hi

Countif will count hidden rows e.g =COUNTIF(D4:D7,"=true") will count then
range including row 6 that has been hidden.

if you wanted to know how many rows are hidden in the worksheet then this
macro will give provide the information.

Sub HiddenRows()
Selection.SpecialCells(xlCellTypeLastCell).Select
lastrow = ActiveCell.Row
range(Cells(1, 1), Cells(lastrow, 1)).Select
For i = 1 To lastrow
If Rows(i).Hidden Then
Hrows = Hrows + 1
End If
Next
Cells(1, 1).Select
x = MsgBox("N Hidden rows" & vbTab & Hrows, vbInformation, "Hidden rows In
Sheet")

End Sub

Regards
Peter
 
B

Billy Liddel

Or more interesting is count the visible items only with a UDF

Function CountVis(data As range, Crit As Variant) As Long
Dim c, count As Long
For Each c In data
If c.Rows.Hidden Then

ElseIf Not c.Rows.Hidden And c = Crit Then
count = count + 1
End If
Next
CountVis = count
End Function
 
T

Telecom

Thnaks for that.

I was hoping to have a running total at the top of the column. I need to
filter the sheet so some rows will be hidden at times. Is there any way I can
do a subtotal and countif combined?
 
B

Billy Liddel

Hi

Have a look at Excell Help on subtotals, type 2 is count including hidden
items while type 102 does not include hidden items. If you have you own
formulas within the data, these are ignored so that double counting is
ignored.
in the subtotal =SUBTOTAL(9,D2:D3), 9 is the type you can change manually to
2 to get a count.

I've, pasted the the list from the Help files below. Hope this helps

Peter
Function_num (includes hidden values) Function_num (ignores hidden values)
Function
1 101 AVERAGE
2 102 COUNT
3 103 COUNTA
4 104 MAX
5 105 MIN
6 106 PRODUCT
7 107 STDEV
8 108 STDEVP
9 109 SUM
10 110 VAR
11 111 VARP
 
T

Telecom

Thanks for that but:

I am counting a list of unique text values not numbers so I can't use
subtotal.
 
B

Billy Liddel

Hi

Yes you can. Say the text column header is called Type, then choose Type as
the field to change and use count as the function to use.

Regards
Peter
 
J

Jerry W. Lewis

This presumes a recent version of Excel, and propagates misleading statments
from Help. SUBTOTAL with 100+ arguments does not ignore hidden cells, it
ignores cells in hidden rows (fortunately that is consistent with the OP's
application). If cells are hidden by hiding columns, they will not be
ignored by SUBTOTAL.

VBA code that can recognize all hidden cells (but are not automatically
recalculated when rows/columns are hidden/unhidden) is discussed at
http://support.microsoft.com/kb/150363

Jerry
 
B

Billy Liddel

Hi

I'm always finding out I know even less than I thought, I wrote my own UDF
but I forgot about Columns and did not realise your point.

Peter
 
Top