How to count rows after filter?

[Ken Spiker]

I have an Excel spreadsheet set up as a database with about 30 fields and
several thousand records. I can filter the database for all records with F
under gender, but I need to know exactly how many those records are.
Unfortunately the filter retains the original numbers in the far left column
and just leaves out the numbers of records which don't appear. So how do I
know exactly how many records are left after the filtering process? Thanx,

Ken

 

[Domenic]

Assuming that Column B contains the gender, try...

=SUBTOTAL(3,B2:B100)

Adjust the range accordingly.

Hope this helps!

 

[CyberTaz]

Hi Ken -

Why bother with the AutoFilter? It doesn't offer a count, but you might try:

=COUNTIF(J5:J3000,"F")
[where J5:J3000 assumes the "gender" field is Column J, rows 5 through 3000
and "F" is the conditional criteria for counting the record]

in the cell of your choice.

 

[Slipface]

Hi Ken -

Why bother with the AutoFilter? It doesn't offer a count,

Set it up as a list with autofilters, and enable the "Total Row" (one
click on the list toolbar), and you'll have all the counts for any or
every column in no time

_d

 

[Slipface]

Quite true, but the OP indicated he has "thousands" of records. Since the
Total Row is at the bottom of the filtered list it would still be a long way
to go just to find out how many records were being displayed.

I use cmd-end to get to mine. But you're correct in that this isn't
nenecesarily the most elegant solution. If I'm working in the middle
of the list, jumping to the end is easy with cmd-end but getting back
to where I was isn't

_d

 

[CyberTaz]

No criticism intended - elegance is often unattainable

If not familiar with it, you might also want to try either Cmd or Ctrl (both
do the same thing) +Dn/Up Arrow rather than Cmd/Ctrl+End/Home. That will
take you to the last/first row in the list without leaving the current
column.

I agree that it would be nice to have a "Return to Previous Cell" command,
but the closest I've found is using the Go To dialog box - which only tracks
the locations you've visited using *it* - or using the Row Edit Box on a
toolbar - which counts from the row your list starts in & isn't based on
actual Row Heading Numbers. I wouldn't be surprised if someone has a VBA
solution, though.

Regards |:>)
Bob Jones
[MVP] Office:Mac

 

[JE McGimpsey]

I agree that it would be nice to have a "Return to Previous Cell" command,

Here's a real quick and dirty add-in (stripped down from a much more
complex application):

ftp://ftp.mcgimpsey.com/excel/rtp.xla

The add-in creates a toolbar with a button that, when clicked, causes XL
to select the immediately previous selection.

The button calls the add-in's ReturnToPreviousCell macro. You could
instead assign it to a keyboard shortcut, or a menu command.

The project's unprotected, so you can look at what I've done. It can
certainly be improved significantly, with more error checking. I suspect
it doesn't play well when using multiple workbooks/worksheets (the app
had a lot of additional stuff going on when workbooks or sheets were
changed), but I didn't test it extensively...

 

[Posterizer]

Here's a real quick and dirty add-in (stripped down from a much more
complex application):

ftp://ftp.mcgimpsey.com/excel/rtp.xla

The add-in creates a toolbar with a button that, when clicked, causes XL
to select the immediately previous selection.

The button calls the add-in's ReturnToPreviousCell macro. You could
instead assign it to a keyboard shortcut, or a menu command.

This is a neat little gizmo--thanks for sharing. Is there a way to
force its menubar to permanently stay in the same spot on the screen? I
"docked" it on top, to the right of one of my existing menus, but every
time I start Excel, it appears right in the middle of the screen

_d