Count unique entries with conditions
Closed
KajFrilander
Posts
3
Registration date
Sunday September 16, 2012
Status
Member
Last seen
September 17, 2012
-
Sep 17, 2012 at 01:13 AM
KajFrilander Posts 3 Registration date Sunday September 16, 2012 Status Member Last seen September 17, 2012 - Sep 17, 2012 at 06:31 AM
KajFrilander Posts 3 Registration date Sunday September 16, 2012 Status Member Last seen September 17, 2012 - Sep 17, 2012 at 06:31 AM
Related:
- Count unique entries with conditions
- How to remove registry entries of uninstalled software - Guide
- Excel create unique id for each row ✓ - Excel Forum
- Youtube subscriber count - Guide
- Unique code to confirm your identity on facebook - Facebook Forum
- How to count names in excel - Guide
2 responses
Mehedad
Posts
22
Registration date
Thursday April 19, 2012
Status
Member
Last seen
April 16, 2013
3
Sep 17, 2012 at 04:12 AM
Sep 17, 2012 at 04:12 AM
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","")
You can write in an additional column:
=IF(COUNTIFS(A:A,A2,B:B,B2,C:C,C2)=1,"UNIQUE","")
KajFrilander
Posts
3
Registration date
Sunday September 16, 2012
Status
Member
Last seen
September 17, 2012
Sep 17, 2012 at 06:31 AM
Sep 17, 2012 at 06:31 AM
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
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
