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

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:A40)-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" <FranciscoRodriguez@discussions.microsoft.com> wrote 
in message news:4F685ABC-5A47-4DC0-ADF2-FCEA18E73B19@microsoft.com...
> 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 

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:A40)-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" <FranciscoRodriguez@discussions.microsoft.com> wrote 
> in message news:4F685ABC-5A47-4DC0-ADF2-FCEA18E73B19@microsoft.com...
> > 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 
> 
> 
> .
> 

You're welcome. Thanks for the feedback!

-- 
Biff
Microsoft Excel MVP


"Francisco Rodriguez" <FranciscoRodriguez@discussions.microsoft.com> wrote 
in message news:3C0F7DEB-E697-4BE2-84E5-C2A70B6A001B@microsoft.com...
> 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:A40)-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" <FranciscoRodriguez@discussions.microsoft.com> 
>> wrote
>> in message news:4F685ABC-5A47-4DC0-ADF2-FCEA18E73B19@microsoft.com...
>> > 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
>>
>>
>> .
>>