Count displayed rows when using filters

[Bobby Stiklus]

Is there a set of worksheet functions analogous to the COUNTx functions, but that count only data dislayed when using AutoFilter

Solutions involving SUBTOTALS, or specially coded value in various columns are not feasible due to the data sequence and dynamic requirements of the application

Feasible solutions will include worksheet functions and formulae for use within the functions, as well as instructions on (or pointers to instructions on) how to create my own set of functions

Does anybody know why this capability is so elusive in standard Excel

Thanks in advanc
Bobb

 

[Ken Wright]

Other than VBA, using the SUBTOTAL() function in some respect is the only way
you will solve this one, I think. That is what it was designed for, so I guess
I'm struggling to see why you would discount it so lightly and then ask for
something else that does the same job. It allows for arguments that will then
perform a COUNT, or SUM, or AVERAGE etc, to name but a few. Excel 2003 has
introduced additional arguments that will also allow manually hidden rows to be
ignored also.

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

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

Is there a set of worksheet functions analogous to the COUNTx functions, but

that count only data dislayed when using AutoFilter?

Solutions involving SUBTOTALS, or specially coded value in various columns are

not feasible due to the data sequence and dynamic requirements of the
application.

Feasible solutions will include worksheet functions and formulae for use

within the functions, as well as instructions on (or pointers to instructions
on) how to create my own set of functions.

 

[Bobby Stiklus]

Thanks for your response. It seems that my hop

I did not lightly discount SUBTOTAL. The fact that the "look and feel" of data...subtotals does not lend itself to the application notwithstanding, my understanding is that it requires both ordered data and pre-knowlege of exactly what you want to subtotal on. This is not unreasonable considering what the function is intended to do, but it is not what I am trying do. Additionally, the requirement of ordered data and pre-knowledge of hierarchical level breaks is be nature at odds with the of AutoFilter within this application. If my understanding is incorrect or inadequate, I am quite willing to learn. For example, Is the subtotal function you refer to not the data...subtotals one

It is important to remember that the data are not hierarchical, nor are the user-filtered values at any given time necessarily relational in the strict database sense: to a great exentent the nature of the selections is associative (and relational only in a mechanical sense)

This is a "lets poke around and look at what we've got and see what we find" sort of application. The data has no specific need to be even orderable, let alone ordered

Ultimately, the last question of my post stands: why is it so difficult to get Excel to understand that the data set under consideration is not the entire data set of the worksheet, but rather just the subset which is displayed

 

[Bobby Stiklus]

Sorry, that response got away from me before I was quite finished with it

The unfinished sentence should read "It seems that my hope lies in the additional parameters of Excel 2003."

 

[AlfD]

Hi!

If you are in "poke around and see" mode, you might try what I usuall
use.

Set up the Autofilter.
Select a column and click on its column letter.
Ensure that the count/sum/average thing (I don't know what it i
called!!) in the embossed bit of the status bar is set to Count.

It should now tell you how many items there are in your list.
Apply a filter.
The number will now change to the number selected by the filter.
It is reversible, cumulative...

Al