count unique in one column based on two other columns

[casey]

Consider the following data:

Year Code1 Code2
A B C
2008 123 08-22
2007 123 07-15
2008 123 08-56
2008 456 08-71
2007 456 07-02
2008 123 08-56

I want to return the count of unique entries for 2008 based on Code1=123
(ans=2; 08-22 and 08-56)

I understand, and am using, the {=COUNT(1/FREQUENCY........} array formula
with named ranges for Cols B & C but cannot seem to add that extra column for
the year.

thx,

 

[new1]

Consider the following data:

Year       Code1   Code2      
   A           B           C
2008       123       08-22
2007       123       07-15
2008       123       08-56
2008       456       08-71
2007       456       07-02
2008       123       08-56

I want to return the count of unique entries for 2008 based on Code1=123
(ans=2; 08-22 and 08-56)

I understand, and am using, the {=COUNT(1/FREQUENCY........} array formula
with named ranges for Cols B & C but cannot seem to add that extra columnfor
the year.  

thx,

Hello,

What about creating an intermediate column containing a formula that
concatenates the year and Code1 ?
Then you can count the number of entries of this new data.

HTH

new1@[no/spam]realce.net

 

[T. Valko]

Assuming no empty cells in the range.

A10 = 2008
B10 = 123

Array entered** :

=COUNT(1/FREQUENCY(IF(A2:A7=A10,IF(B2:B7=B10,MATCH(C2:C7,C2:C7,0))),ROW(C2:C7)-MIN(ROW(C2:C7))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[casey]

Biff,
Worked perfectly. Thanks as always.
casey

New1,
I'm sure yours would work but since i was most familiary with the
"COUNT(1/FREQUENCY..." formula, I used Biff's instead. Thanks, too, for your
response.
casey

 

[T. Valko]

You're welcome. Thanks for the feedback!