count of autofilter 'choices'

[Guest]

Is there any (possibly in a VB macro) way to get the count of values which appear in an autofilter dropdown?

 

[Dave Peterson]

The size of that list is always going to be less than 1000 (excel's limit for
displaying the list).

Chip Pearson has a formula that counts unique items in a list at:
http://www.cpearson.com/excel/duplicat.htm

look for:
Counting Unique Entries In A Range

 

[Dave Peterson]

It sounds like one of your formulas/macros that count unique (with or without
blanks) would give you that number (unless that number exceeds 1000).

 

[Bob Phillips]

Jake,

This doesn't count the number of entries in a filter, but it does count the
unique entries in a range. As filter only has uniques + blanks + all, it's
easy to deduce from this.

=SUM(IF(LEN(A1:A20),1/COUNTIF(A1:A20,A1:A20)))

It is array formula, so enter with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Is there any (possibly in a VB macro) way to get the count of values which

appear in an autofilter dropdown?

 

[Peo Sjoblom]

Count in an autofilter is

=SUBTOTAL(2,Range)

numeric

or

=SUBTOTAL(3,Range)

all values