count unique values with auto-filter on
 

Hello,

I'm trying to count unique numbers in a resulting list after filtering. I
have already learned how to count unique number in an unfiltered list using:

=SUM(IF(FREQUENCY($A$12:$A40,$A$12:$A40)0,1))

but now I need to know how many unique numbers remain after I filter for
another column. Help!

Cisco

count unique values with auto-filter on
 

Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A12:A40,ROW (A12:A40)-ROW(A12),0,1)),MATCH(A12:A40,A12:A40,0)),ROW(A12:A 40)-ROW(A12)+1)0,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Francisco Rodriguez" wrote
in message ...
Hello,

I'm trying to count unique numbers in a resulting list after filtering. I
have already learned how to count unique number in an unfiltered list
using:

=SUM(IF(FREQUENCY($A$12:$A40,$A$12:$A40)0,1))

but now I need to know how many unique numbers remain after I filter for
another column. Help!

Cisco

count unique values with auto-filter on
 

That worked like a charm. Thanks for your help and expertise!

Francisco

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A12:A40,ROW (A12:A40)-ROW(A12),0,1)),MATCH(A12:A40,A12:A40,0)),ROW(A12:A 40)-ROW(A12)+1)0,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Francisco Rodriguez" wrote
in message ...
Hello,

I'm trying to count unique numbers in a resulting list after filtering. I
have already learned how to count unique number in an unfiltered list
using:

=SUM(IF(FREQUENCY($A$12:$A40,$A$12:$A40)0,1))

but now I need to know how many unique numbers remain after I filter for
another column. Help!

Cisco


.

count unique values with auto-filter on
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Francisco Rodriguez" wrote
in message ...
That worked like a charm. Thanks for your help and expertise!

Francisco

"T. Valko" wrote:

Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A12:A40,ROW (A12:A40)-ROW(A12),0,1)),MATCH(A12:A40,A12:A40,0)),ROW(A12:A 40)-ROW(A12)+1)0,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Francisco Rodriguez"
wrote
in message ...
Hello,

I'm trying to count unique numbers in a resulting list after filtering.
I
have already learned how to count unique number in an unfiltered list
using:

=SUM(IF(FREQUENCY($A$12:$A40,$A$12:$A40)0,1))

but now I need to know how many unique numbers remain after I filter
for
another column. Help!

Cisco


.

[quote=T. Valko;914788]Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(2,OFFSET(A12:A40,ROW (A12:A40)-ROW(A12),0,1)),MATCH(A12:A40,A12:A40,0)),ROW(A12:A 40)-ROW(A12)+1)0,1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP

Hi Biff,

your formula work very well..

Many thank and best regards,

Giulio (Italy)