MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### count unique values with auto-filter on

• Follow

```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
```
 0

```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
> 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

```
 0

```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
> > 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
>
>
> .
>
```
 0

```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
>> > 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
>>
>>
>> .
>>

```
 0

3 Replies
510 Views

Similiar Articles:

7/20/2012 7:26:07 AM