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.

 

[T. Valko]

The easiest way to count ONLY cells that are NOT hidden by a filter is this: =subtotal(103,A1:A100,1)

If you use =subtotal(3,A1:A100,1) it will count both visible AND hidden rows.

Look in HELP - Subtotal function sytax - function number. Cool things hidden there...SUM, MAX, MIN, PRODUCT, COUNT, COUNTA... And you can adapt to having only filtered data in a range considered...or all data in a range considered!

Note that the 100 series SUBTOTAL arguments are only available in Excel 2003 and later.

 

[ChrisHaley81]

Hi, I know this thread has been quiet a long time but I am having a problem, I have a formula in Excel which uses Countifs but I want it to only to it to the filtered results. I have tried to use the code on the first page for the Vis & COUNTIFv but I am struggling to modify this for Countifs. Thanks.

 

[Flynnwil]

Re: 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

Hi,

This is a super piece of code. Is the same thing possible with a "countifs" function?
greetings,
William