Method for unique lookup w/ condition

[Engineers08]

I'm looking for a method to lookup the unique values in a column, and count them by a second criteria. An example is shown below, and I will upload a small snippit of my document.

Column A - Column B
a A
a B
b B
c A
d A
d C
d C
e A
e A
e A
f A
f B
f C


If I were looking up the unique values FOR A, my expected result would be 6 (consiting of a,b,c,d,e,f). If I was looking it up for B, the expected result would be 3 (a,b,f). And for C the anwser would be 2 (d,f).


This is the forumla I tried to use, but results in an error becuase the second array returns a number other than 1 or 0.

=SUMPRODUCT(--('Calculated Data'!$C$2:$C$10000=A2),--(COUNTIF('Calculated Data'!$C$2:$C$10000,A2)))


Thanks!

`engineers08

[Teethless mama]

I'm looking for a method to lookup the unique values in a column, and count them by a second criteria. An example is shown below, and I will upload a small snippit of my document.

Column A - Column B
a A
a B
b B
c A
d A
d C
d C
e A
e A
e A
f A
f B
f C


If I were looking up the unique values FOR A, my expected result would be 6 (consiting of a,b,c,d,e,f). If I was looking it up for B, the expected result would be 3 (a,b,f). And for C the anwser would be 2 (d,f).


This is the forumla I tried to use, but results in an error becuase the second array returns a number other than 1 or 0.

=SUMPRODUCT(--('Calculated Data'!$C$2:$C$10000=A2),--(COUNTIF('Calculated Data'!$C$2:$C$10000,A2)))


Thanks!

`engineers08

See the attached

[Engineers08]

Wow! Toothless, you did a hell of a job there. I never would have thought to use the match functions in conjunction with the frequency statement. That was awesome!


Thanks so much, you made my day a LOT better.

`engineers08