Count unique entries with conditions

[Closed]
Report
Posts
3
Registration date
Sunday September 16, 2012
Status
Member
Last seen
September 17, 2012
-
Posts
3
Registration date
Sunday September 16, 2012
Status
Member
Last seen
September 17, 2012
-
Hello,

I have a problem that I have pondering for a while now. I would need to count the number of unique entries in my data with some pre-determined conditions. I am using excel 2007. I am asking for your insight on the following:

Data description:

My data has two columns of names. I would need to count the number of unique names in column A for each individual name and year in columns B and C.

To sum up, I need to calculate the number of unique "Leads" in column A given a specific participant and year.

I hope, that the following file will illustrate my problem:

http://speedy.sh/HzE4V/Number-of-unique-entries.xlsx

Could anyone help me on this? All help, tips and leads are appreciated.

Thank you for your help in advance!

Best regards,

Kaj




2 replies

Posts
22
Registration date
Thursday April 19, 2012
Status
Member
Last seen
April 16, 2013
3
You can use the "COUNTIFS" function. The site is blocked in my office so couldn't see what the data looks like.
You can write in an additional column:
=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2)=1,"UNIQUE","")
Posts
3
Registration date
Sunday September 16, 2012
Status
Member
Last seen
September 17, 2012

Hello,

Thank you for the quick reply. However, I am afraid that the formula does not really answer to my need.

I try to clarify.

Here is sample of my data:

Column A| Column B | Column C
Bank 1 | Firm 1 | 2011
Bank 1 | Firm 2 | 2009
Bank 2 | Firm 2 | 2011
Bank 3 | Firm 3 | 2008
Bank 1 | Firm 1 | 2011
Bank 4 | Firm 4 | 2011

I can calculate the number of unique values (number of different banks) in Column A with the following formula:

=SUM(IF(FREQUENCY(MATCH(A2:A7;A2:A7;0);MATCH(A2:A7;A2:A7;0))>0;1))

By using that formula, I obtain the value 4 (four unique banks).

However, I would need to calculate in each cell in Column D the number of unique occurrences in Column A given the values in Columns B and C:

Column A| Column B | Column C | Column D
Bank 1 | Firm 1 | 2011 | 2 (rows 2 and 6)
Bank 1 | Firm 2 | 2009 | 2 (rows 3, 4 and 7. Only two unique)
Bank 2 | Firm 2 | 2009 | 2 (rows 3, 4 and 7. Only two unique)
Bank 3 | Firm 3 | 2008 | 1 (row 4)
Bank 7 | Firm 1 | 2011 | 2 (rows 2 and 6)
Bank 2 | Firm 2 | 2009 | 2 (rows 3, 4 and 7. Only two unique)

Hopefully this clarified my problem. I appreciate any help you can provide. Thank you in advance!

Best, Kaj