Counting From an AutoFilter

[supanoods]

I need to be able to count how many diffrenent values there are when i use an auto filter. For example:

The contents of my column are: 3,4,8,8,5,4,6,3,8,3,2

therefore the filter when clicked shows :
3
4
5
6
8
and that means that there are 5 DIFFERENT values.

Does anybody know how to do this using VBA??

Cheers

Phasid "If something is hard to do - then it isn't worth doing" - Homer Simpson

 

[RobBroekhuis]

I suspect you may need to write your own custom function for this, which would be fairly straightforward. But I may be wrong...
Rob

 

[DaleWatson123321]

Phasid,

Here's an "array" formula that will count the number of different (unique) numbers in a range named "data".

=SUM(IF(COUNTIF(data,data)=1,1,IF(ISBLANK(data),0,1/(COUNTIF((data),data)))))

IMPORTANT Steps:

1) First assign the range name "data". If you prefer, you can extend the range name beyond the current range containing numbers - i.e. the range name can include "blank" rows.

2) Copy the formula from Tek-Tips to your worksheet.

3) Being an "array" formula, you MUST use the following method of "entering" this special type of formula:
a) Hit the &quot;Edit&quot; key <F2>
b) While holding down the <Control> and <Shift> keys, hit <Enter>

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[robcarr]

to count the data in a filter you use the =subtotal() command this will then allow different functions for counting filtered data, below is the list of functions for use with subtotal.

1 AVERAGE
2 COUNT
3 COUNTA
4 MAX
5 MIN
6 PRODUCT
7 STDEV
8 STDEVP
9 SUM
10 VAR
11 VARP

hope this helps.
Thanks Rob.