Have a formula consider only visible rows

[tk19]

I have a formula, something like

=COUNTIF(A1:A100,1)

Which will count all cells equal to 1. Is there a way I can have it only count the visible (i.e., its row is not hidden) cells that are equal to 1?


I actually had another question, whether I can do conditional formatting that will either hide a row or change its row height based on the value of a particular cell in that row. I'm guessing that the only way I can do that is through a macro in the Workbook_Change() event.

 

[Joe4]

You can write a custom function to do that:

Function MyRowCount(MyRange As Range) As Integer

    Dim c As Range
    For Each c In MyRange
        If (c.Value = 1) And (c.EntireRow.Hidden = False) Then
            MyRowCount = MyRowCount + 1
        End If
    Next c
        
End Function

Then, use it like any other function.

For example, if you wanted to count the number of visible rows equal to 1 in the range A1 to A10, simply use this formula on the spreadsheet:

=MyRowCount(A1:A10)

 

[Damon Ostrander]

COUNTIF that doesn't count hidden cells

Hi tk19,

If you are interested, here is a more general COUNTIF solution, and one that you can also apply to SUM and other functions that operate on ranges of cells.

This COUNTIFv UDF uses the worksheet function COUNTIF to count visible cells only, so the Condition argument works the same as with COUNTIF. So you can use it just as you would COUNTIF:

=COUNTIFv(A1:A100,1)

Note that it uses a helper function (Vis) that returns the disjoint range of visible cells in a given range. This can be used with other worksheet functions to cause them to operate only on the visible cells. For example,

=SUM(Vis(A1:A100))

yields the sum of the visible cells in A1:A100. The reason why this approach of using Vis directly in the argument list does not work with COUNTIF is that COUNTIF will not accept a disjoint range as an input, whereas SUM will.

Here's the UDF code:

Function Vis(Rin As Range) As Range
'Returns the subset of Rin that is visible
Dim Cell As Range
Application.Volatile
Set Vis = Nothing
For Each Cell In Rin
If Not (Cell.EntireRow.Hidden Or Cell.EntireColumn.Hidden) Then
If Vis Is Nothing Then
Set Vis = Cell
Else
Set Vis = Union(Vis, Cell)
End If
End If
Next Cell
End Function

Function COUNTIFv(Rin As Range, Condition As Variant) As Long
'Same as Excel COUNTIF worksheet function, except does not count
'cells that are hidden
Dim A As Range
Dim Csum As Long
Csum = 0
For Each A In Vis(Rin).Areas
Csum = Csum + WorksheetFunction.CountIf(A, Condition)
Next A
COUNTIFv = Csum
End Function

 

[tk19]

Thanks to both of you. I actually used Damon's more general solution, and it works great.

 

[BlackGaff]

I've tried implementing the Vis function into excel '03, and find the program takes a performance hit when hiding/showing rows.

Noting this is an older thread, and not being an experienced programmer in the least, is there a work around to reduce the performance hit?

 

[wisconsinben]

I am using Damon Ostrander's COUNTIFv and Vis functions with great sucess! However, I have a workbook that also contains a button and associated macro to insert a row at a particular location. Whenever I run this macro (by clicking the button) all of the formulas using the COUNTIFv and Vis functions return #VALUE!

Here is the code for the macro:

Sub AddEntry()Dim CurRegion As Range
Dim FirstCell As Range
Dim LastCell As Range
Dim Counter As Long
Dim NumRows As LongSet CurRegion = Range("A2").CurrentRegion
NumRows = CurRegion.Rows.Count
Set FirstCell = CurRegion.Resize(1, 1) 'Top Left
Set LastCell = FirstCell.Offset(NumRows - 1, 0) 'Bottum Left
LastCell.Offset(1, 0).EntireRow.Insert Shift:=xlShiftDown 'Insert right after lastCell
End Sub
 
Private Sub CommandButton1_Click()
End Sub

Any help would be appreciated!

 

[wisconsinben]

Nevermind my question above. I inserted

Application.Calculate

at the end of my macro to recalculate all of the cell values after inserting the row, and this took care of the problem I was having.

 

[jma49]

Re: COUNTIF that doesn't count hidden cells

Using the Vis function with Excel's averageif function and it works most of the time. Sometimes I receive a #Value error when I hide a row(Apply a filter). Other times it works fine. Any ideas where I can look to make this more stable? thks...John

 

[T. Valko]

Re: COUNTIF that doesn't count hidden cells

Using the Vis function with Excel's averageif function and it works most of the time. Sometimes I receive a #Value error when I hide a row(Apply a filter). Other times it works fine. Any ideas where I can look to make this more stable? thks...John

If you're using the AutoFilter then you can use worksheet functions to get an "average if".

What's the full unfiltered range to be averaged and what is the condition?

 

[jma49]

Further explanation is in order. I have two worksheets. Worksheet1 contains the data. The table range is A2:CH5. Row 2 contains the headers and rows 3 thur 5 contains the data. Table name is Table1. Worksheet2 contains a summary of the data. In cell H2 on sheet2 I want to show the average of the visible rows for field3 if the value of field5 for each visible row is equal to 18. The formula that I am using in cell H2 of sheet2 is =Averageif(vis(Table1[Field5]),18,vis(Table1[Field3])). If I remove the vis functyion from the equation I always the the average of all three rows. If I filter out (hide) a row I want the average to only reflect the other two rows (the visible rows). The vis function is the one that I got from this thread.

Hope this clears things up and thanks in advance for your interst and willingness to help.