Count of filtered rows with blanks

  • Thread starter Thread starter Guest
  • Start date Start date
G

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


DOTjake said:
How can I get a count of rows, including blanks, showing after a filter is
Click to expand...
applied. I can do it with a macro but would prefer a formula.
 
It won't count blanks

--

Regards,

Peo Sjoblom

Aladin Akyurek said:
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.
Click to expand...
 
I see I missed "blanks"...

I think we need more info on what this counting in blanks involves.
 
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
 
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,D2:D200)

that will "count" the blanks in A2:A200

--

Regards,

Peo Sjoblom


DOTjake said:
Counting blanks means that some of the rows may have blanks in them, or
Click to expand...
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
Click to expand...
=COUNTA($A$6:$A$10)+COUNTBLANK($A$6:$A$10) to account for the blanks
 
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
 
Back
Top