counting rows when using filtering: how??

[Guest]

I have a basic spread sheet of contact information...thousands of
records. I use filtering to group the information based on various
criteria, but I would also like to count the number of rows (records)
after I filter the whole data set. The usual count functions don't
ignore the hidden rows...which is what filtering does...hide some
things. Consequently, all I get is the total number of rows and not
just those the filter displays.

I saw this counting done, so I know it is possible, but I no longer
know where the person is who knew how to do this. Is there a function
that will do this or did the person create a program for it?? I seem
to recall it was latter.

Any help would be appreciated.

Henry

 

[Bob Phillips]

=SUMPRODUCT((SUBTOTAL(3,OFFSET($B$1,ROW($B$2:$B$20)-ROW($B$1),,1))))

wher B is the filtered column

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Take a look at =subtotal() in help.

It'll ignore those rows hidden by an autofilter.

 

[Domenic]

You can use the SUBTOTAL function. Have a look at Excel's help file...

Hope this helps!

 

[Ken Wright]

Note that in XL 2003 new arguments have been added so that the SUBTOTAL
function can be made to ignore manually hidden rows as well as filtered
ones.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission
------------------------------­------------------------------­----------------

 

[Guest]

Thanks for the tips. I will look into these options and feed back.
These options didn't appear to come up when I queried Help on
countiing rows with text info rather than numerical data...which
subtotal "use" to be used for. I even went so far as to add an extra
column with "1" in each cell so I "could" use some numerical counting,
but the hidden rows foiled that too.

Excel is really a neat product with many more bells and whistles than
are apparent...I am also looking at some Monte Carlo calculations on
some data...separate issue...and I think Excel can handle that to some
extent. Viva Excel!

Henry

 

[Guest]

I used the simpler subtotal functioin...and it works well enough. The
counts are accumulated in a bit of a kludgy manner, but it works for
me. Thank you for helping me out.

Henry