Unique Count sensitive to hidden/filtered rows

Hello.

I need to count unique values in a list. I have many ways to do this (I am
currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0,
1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine.

But - I wish to be able to filter the list and have the function adjust to
only the visible cells.

I tried substituting the SUBTOTAL function for SUM in the formula above, but
I get an ERROR result.

I am willing to use intermediate columns if I must. Can anyone help?

One way albeit rather complicated

http://tinyurl.com/9rfmv


Regards,

Peo Sjoblom

"Ian" wrote:

> Hello.
>
> I need to count unique values in a list. I have many ways to do this (I am
> currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0,
> 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine.
>
> But - I wish to be able to filter the list and have the function adjust to
> only the visible cells.
>
> I tried substituting the SUBTOTAL function for SUM in the formula above, but
> I get an ERROR result.
>
> I am willing to use intermediate columns if I must. Can anyone help?

Thanks for the reply, Peo. I tried using the following function (the one you
pointed to with my range substituted):

=SUM(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(ROW(A2:A100)),,1)),(MMULT(((A2:A100=TRANSPOSE(A2:A100)*TRANSPOSE(SUBTOTAL(3,OFFSET(A2,ROW(A2:A100)-MIN(ROW(A2:A100)),,1))))*(ROW(A2:A100)>=TRANSPOSE(ROW(A2:A100)))),ROW(A2:A100)*0+1)
=1)*1))

It looks like that requires numeric entries (MMULT function)? I get a #VALUE
error. I have text entries in the cells I am trying to count.

Any other ideas, anyone, or can you help me understand what I did wrong
translating the idea provided by Peo?

-- Ian

"Peo Sjoblom" wrote:

> One way albeit rather complicated
>
> http://tinyurl.com/9rfmv
>
>
> Regards,
>
> Peo Sjoblom
>
> "Ian" wrote:
>
> > Hello.
> >
> > I need to count unique values in a list. I have many ways to do this (I am
> > currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0,
> > 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine.
> >
> > But - I wish to be able to filter the list and have the function adjust to
> > only the visible cells.
> >
> > I tried substituting the SUBTOTAL function for SUM in the formula above, but
> > I get an ERROR result.
> >
> > I am willing to use intermediate columns if I must. Can anyone help?

Thanks for the reply, Peo. Unfortunately, I must be doing something wrong. I
get a #VALUE error.

Doest the formula you referenced require numeric values only? I see that it
uses the MMULT function.

Thanks again!!! -- Ian

"Peo Sjoblom" wrote:

> One way albeit rather complicated
>
> http://tinyurl.com/9rfmv
>
>
> Regards,
>
> Peo Sjoblom
>
> "Ian" wrote:
>
> > Hello.
> >
> > I need to count unique values in a list. I have many ways to do this (I am
> > currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0,
> > 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine.
> >
> > But - I wish to be able to filter the list and have the function adjust to
> > only the visible cells.
> >
> > I tried substituting the SUBTOTAL function for SUM in the formula above, but
> > I get an ERROR result.
> >
> > I am willing to use intermediate columns if I must. Can anyone help?

Try...

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9:A1000,ROW(A9:A1000)-MIN(ROW(A9:
A1000)),0,1)),MATCH(A9:A1000,A9:A1000,0)),IF(SUBTOTAL(3,OFFSET(A9:A1000,R
OW(A9:A1000)-MIN(ROW(A9:A1000)),0,1)),MATCH(A9:A1000,A9:A1000,0)))>0,1,0)
)

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

In article <69973F6A-3216-4F2B-A475-9BE0787372CE@microsoft.com>,
Ian <Ian@discussions.microsoft.com> wrote:

> Hello.
>
> I need to count unique values in a list. I have many ways to do this (I am
> currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0,
> 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine.
>
> But - I wish to be able to filter the list and have the function adjust to
> only the visible cells.
>
> I tried substituting the SUBTOTAL function for SUM in the formula above, but
> I get an ERROR result.
>
> I am willing to use intermediate columns if I must. Can anyone help?

Yes it does, but if you have let's say names in A2:A300 and use filter and
want to count unique names in the filtered list you can add a help column
and include it in the filter, i.e. in the help column put

=COUNTIF($A$2:A2,A2)=1


copy down as long as needed, now include the help column in the filter and
filter on TRUE, then just count the TRUE with =SUBTOTAL(3,E2:E300)

where E2:E300 would be the help column with the countif formulas

will give you # of unique names

--
Regards,

Peo Sjoblom


"Ian" <Ian@discussions.microsoft.com> wrote in message
news:4A7A7CAE-503A-4F91-8B1E-95FBA2CCB0C0@microsoft.com...
> Thanks for the reply, Peo. Unfortunately, I must be doing something wrong.
> I
> get a #VALUE error.
>
> Doest the formula you referenced require numeric values only? I see that
> it
> uses the MMULT function.
>
> Thanks again!!! -- Ian
>
> "Peo Sjoblom" wrote:
>
>> One way albeit rather complicated
>>
>> http://tinyurl.com/9rfmv
>>
>>
>> Regards,
>>
>> Peo Sjoblom
>>
>> "Ian" wrote:
>>
>> > Hello.
>> >
>> > I need to count unique values in a list. I have many ways to do this (I
>> > am
>> > currently using an array function
>> > {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0,
>> > 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine.
>> >
>> > But - I wish to be able to filter the list and have the function adjust
>> > to
>> > only the visible cells.
>> >
>> > I tried substituting the SUBTOTAL function for SUM in the formula
>> > above, but
>> > I get an ERROR result.
>> >
>> > I am willing to use intermediate columns if I must. Can anyone help?

What range is being filtered, and on what column and value?

Alan Beban

Ian wrote:
> Thanks for the reply, Peo. Unfortunately, I must be doing something wrong. I
> get a #VALUE error.
>
> Doest the formula you referenced require numeric values only? I see that it
> uses the MMULT function.
>
> Thanks again!!! -- Ian
>
> "Peo Sjoblom" wrote:
>
>
>>One way albeit rather complicated
>>
>>http://tinyurl.com/9rfmv
>>
>>
>>Regards,
>>
>>Peo Sjoblom
>>
>>"Ian" wrote:
>>
>>
>>>Hello.
>>>
>>>I need to count unique values in a list. I have many ways to do this (I am
>>>currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0,
>>>1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine.
>>>
>>>But - I wish to be able to filter the list and have the function adjust to
>>>only the visible cells.
>>>
>>>I tried substituting the SUBTOTAL function for SUM in the formula above, but
>>>I get an ERROR result.
>>>
>>>I am willing to use intermediate columns if I must. Can anyone help?

This worked perfectly! Thanks very much, everyone for you wonderful help.

-- Ian

"Domenic" wrote:

> Try...
>
> =SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A9:A1000,ROW(A9:A1000)-MIN(ROW(A9:
> A1000)),0,1)),MATCH(A9:A1000,A9:A1000,0)),IF(SUBTOTAL(3,OFFSET(A9:A1000,R
> OW(A9:A1000)-MIN(ROW(A9:A1000)),0,1)),MATCH(A9:A1000,A9:A1000,0)))>0,1,0)
> )
>
> ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER.
>
> Hope this helps!
>
> In article <69973F6A-3216-4F2B-A475-9BE0787372CE@microsoft.com>,
> Ian <Ian@discussions.microsoft.com> wrote:
>
> > Hello.
> >
> > I need to count unique values in a list. I have many ways to do this (I am
> > currently using an array function {=SUM(IF(COUNTIF(A9:A1000,A9:A1000)=0, 0,
> > 1/COUNTIF(A9:A1000,A9:A1000)))}, and that works fine.
> >
> > But - I wish to be able to filter the list and have the function adjust to
> > only the visible cells.
> >
> > I tried substituting the SUBTOTAL function for SUM in the formula above, but
> > I get an ERROR result.
> >
> > I am willing to use intermediate columns if I must. Can anyone help?
>