count unique in one column based on two other columns
 

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,

count unique in one column based on two other columns
 

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

count unique in one column based on two other columns
 

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,

count unique in one column based on two other columns
 

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,

count unique in one column based on two other columns
 

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,