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,

On 18 nov, 21:44, casey wrote:
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,

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

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)

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
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,

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" wrote:

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)

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
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,

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
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" wrote:

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)

--
Biff
Microsoft Excel MVP


"casey" wrote in message
...
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,