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

[Guest]

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.

 

[Guest]

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.

hi

Countif will count hidden rows e.g =COUNTIF(D47,"=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

 

[Guest]

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

 

[Guest]

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?

 

[Guest]

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,D23), 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

 

[Guest]

Thanks for that but:

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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