counting rows when using filtering: how??

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

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
 
=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)
 
Take a look at =subtotal() in help.

It'll ignore those rows hidden by an autofilter.
 
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 :-)
------------------------------­------------------------------­----------------
 
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
 
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
 
Back
Top