count unique in one column based on two other columns


C

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,
 
Ad

Advertisements

N

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

[email protected][no/spam]realce.net
 
T

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)
 
C

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top