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
|