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.

"Telecom" wrote:

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(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

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

Regards
Peter

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?

"Billy Liddel" wrote:

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

Regards
Peter

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

"Telecom" wrote:

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?

"Billy Liddel" wrote:

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

Regards
Peter

Thanks for that but:

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

"Billy Liddel" wrote:

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

"Telecom" wrote:

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?

"Billy Liddel" wrote:

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

Regards
Peter

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

"Telecom" wrote:

Thanks for that but:

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

"Billy Liddel" wrote:

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

"Telecom" wrote:

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?

"Billy Liddel" wrote:

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

Regards
Peter

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

"Billy Liddel" wrote:

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

"Telecom" wrote:

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?

"Billy Liddel" wrote:

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

Regards
Peter

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

"Jerry W. Lewis" wrote:

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

"Billy Liddel" wrote:

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

"Telecom" wrote:

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?

"Billy Liddel" wrote:

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

Regards
Peter