Count of filtered rows with blanks

[Guest]

How can I get a count of rows, including blanks, showing after a filter is applied. I can do it with a macro but would prefer a formula

Thanks
DOTjake

 

[Aladin Akyurek]

Let A2:F100 be the area (including the labels) where you apply AutoFilter.

=SUBTOTAL(3,A3:A100)

or

=IF(COUNTA(A3:A100)<>SUBTOTAL(3,A3:A100),SUBTOTAL(3,A3:A100),"")

How can I get a count of rows, including blanks, showing after a filter is

applied. I can do it with a macro but would prefer a formula.

 

[Peo Sjoblom]

It won't count blanks

--

Regards,

Peo Sjoblom

Let A2:F100 be the area (including the labels) where you apply AutoFilter.

=SUBTOTAL(3,A3:A100)

or

=IF(COUNTA(A3:A100)<>SUBTOTAL(3,A3:A100),SUBTOTAL(3,A3:A100),"")


is
applied. I can do it with a macro but would prefer a formula.

 

[Aladin Akyurek]

I see I missed "blanks"...

I think we need more info on what this counting in blanks involves.

 

[Guest]

Counting blanks means that some of the rows may have blanks in them, or numbers or text. I want to count all rows which are displayed after a filter is applied. I have filters on different columns if that matters.

For my total rows count I have to use =COUNTA($A$6:$A$10)+COUNTBLANK($A$6:$A$10) to account for the blank

DOTjake

 

[Peo Sjoblom]

You probably have to use a help column (of columns depending on if you count
the whole Tables or column by column)and an if function like

=IF(A2="",1,0)

assume that is in a help column in D copied down, then just use

=SUBTOTAL(9,D2200)

that will "count" the blanks in A2:A200

--

Regards,

Peo Sjoblom

Counting blanks means that some of the rows may have blanks in them, or

numbers or text. I want to count all rows which are displayed after a
filter is applied. I have filters on different columns if that matters.

For my total rows count I have to use

=COUNTA($A$6:$A$10)+COUNTBLANK($A$6:$A$10) to account for the blanks

 

[Guest]

I just decided to write a function (VBA

Function CountFiltered(rng As Range) As Lon
Dim ct As Lon
ct =
For Each r In rng.Row
If Not r.Hidden Then ct = ct +
Nex
CountFiltered = c
End Functio

Then use =CountFiltered(A1:A100

Thanks