ExcelBanter - Array formula for unique values

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Array formula for unique values (https://www.excelbanter.com/excel-worksheet-functions/126932-re-array-formula-unique-values.html)

Array formula for unique values

Worked like a charm. Thanks.

Dan

"T. Valko" wrote:

Try this:

D1 = AA
D2 = AB
D3 = AC

Array entered in E1:

=SUM(N(FREQUENCY(IF(A$1:A$12=D1,MATCH(B$1:B$12,B$1 :B$12,0)),MATCH(B$1:B$12,B$1:B$12,0))0))

Copy down to E3

Biff

"Dan Hatola" wrote in message
...
I am trying to create an array formula that will allow me to count the
unique
values in Range2 if the value in Range1 matches a given value. Normally,
I
would use an array formula like =sum(1/countif(Range2,Range2)) to count
uniques however when I tried to add the conditional component to it, I got
a
#VALUE! error.

Here is the array formula I tried:
=sum(1/countif(if(Range1="AA", Range2),if(Range1="AA", Range2)))

Range1 Range2
AA 1
AA 2
AA 3
AA 4
AB 1
AB 1
AB 2
AB 2
AC 1
AC 2
AC 3
AC 2

Below is the output I would like to create (one formula next to each set
of
letters):

AA 4
AB 2
AC 3

Any help would be greatly appreciated.

Dan

Array formula for unique values

You're welcome. Thanks for the feedback!

Biff

"Dan Hatola" wrote in message
...
Worked like a charm. Thanks.

Dan

"T. Valko" wrote:

Try this:

D1 = AA
D2 = AB
D3 = AC

Array entered in E1:

=SUM(N(FREQUENCY(IF(A$1:A$12=D1,MATCH(B$1:B$12,B$1 :B$12,0)),MATCH(B$1:B$12,B$1:B$12,0))0))

Copy down to E3

Biff

"Dan Hatola" wrote in message
...
I am trying to create an array formula that will allow me to count the
unique
values in Range2 if the value in Range1 matches a given value.
Normally,
I
would use an array formula like =sum(1/countif(Range2,Range2)) to count
uniques however when I tried to add the conditional component to it, I
got
a
#VALUE! error.

Here is the array formula I tried:
=sum(1/countif(if(Range1="AA", Range2),if(Range1="AA", Range2)))

Range1 Range2
AA 1
AA 2
AA 3
AA 4
AB 1
AB 1
AB 2
AB 2
AC 1
AC 2
AC 3
AC 2

Below is the output I would like to create (one formula next to each
set
of
letters):

AA 4
AB 2
AC 3

Any help would be greatly appreciated.

Dan