Counting unique values in a column for each value in another column

B

boris1

New Member
Joined
Apr 26, 2011
Messages
8
Office Version
  1. 365
Platform
  1. MacOS
  2. Web
Hello,
Could someone please help me with a formula that would count the unique values in a column for each value in another column?
Here is my sample data. I'd like to count the unique IDs for every city, so 3 for Cologne, 8 for Portland, etc. I was hoping to later sum these values in a Pivot Table so I would just like one sum value for each city or for the formula to output a 1 for a unique and a zero for a non-unique.

Leadfeeder 2020-2022.xlsx
ABCD
1CityIDDesired output
2Colognece7c03uniques for Cologne
3Colognea34d2
4Colognea34d2
5Colognea34d2
6Colognea34d2
7Cologne38df1
8Cologne38df1
9Cologne38df1
10Portland43aff8Uniques for Portland
11Portland437c3
12Portland437c4
13Portland067b2
14Portland067b3
15Portlandbc53a
16Portland437c4
17Portland437c4
18Portlandbc53a
19Portlandbc53a
20Portland9fd7d
21Portland4fd7c
22Portland9fd7d
23Portland9fd7d
24Budapeste8ad36Uniques for Budapest
25Budapestb8a2a
26Budapestb8a2a
27Budapestb8a2a
28Budapest08154
29Budapest08154
30Budapest96b0f
31Budapest96b0f
32Budapestb8a2a
33Budapest61a33
34Budapestf9066
35Budapestf9066
36
Sheet1
Cell Formulas
RangeFormula
C2C2=SUMPRODUCT(1/COUNTIF(B$2:B$9,B$2:B$9))
C10C10=SUMPRODUCT(1/COUNTIF(B$10:B$23,B$10:B$23))
C24C24=SUMPRODUCT(1/COUNTIF(B$24:B$35,B$24:B$35))
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
boris1 said:
Hello,
Could someone please help me with a formula that would count the unique values in a column for each value in another column?
Here is my sample data. I'd like to count the unique IDs for every city, so 3 for Cologne, 8 for Portland, etc. I was hoping to later sum these values in a Pivot Table so I would just like one sum value for each city or for the formula to output a 1 for a unique and a zero for a non-unique.

Leadfeeder 2020-2022.xlsx
ABCD
1CityIDDesired output
2Colognece7c03uniques for Cologne
3Colognea34d2
4Colognea34d2
5Colognea34d2
6Colognea34d2
7Cologne38df1
8Cologne38df1
9Cologne38df1
10Portland43aff8Uniques for Portland
11Portland437c3
12Portland437c4
13Portland067b2
14Portland067b3
15Portlandbc53a
16Portland437c4
17Portland437c4
18Portlandbc53a
19Portlandbc53a
20Portland9fd7d
21Portland4fd7c
22Portland9fd7d
23Portland9fd7d
24Budapeste8ad36Uniques for Budapest
25Budapestb8a2a
26Budapestb8a2a
27Budapestb8a2a
28Budapest08154
29Budapest08154
30Budapest96b0f
31Budapest96b0f
32Budapestb8a2a
33Budapest61a33
34Budapestf9066
35Budapestf9066
36
Sheet1
Cell Formulas
RangeFormula
C2C2=SUMPRODUCT(1/COUNTIF(B$2:B$9,B$2:B$9))
C10C10=SUMPRODUCT(1/COUNTIF(B$10:B$23,B$10:B$23))
C24C24=SUMPRODUCT(1/COUNTIF(B$24:B$35,B$24:B$35))
Click to expand...
I think I should have written Distinct values, not Unique.
 
Upvote 0
@boris1 The below is not the prettiest formula but maybe it will help?
Note that the rows in my starting formula are 2:30
You should use 2 for the start row.
I have used 30 for the end row.
You need to use ? where ? is more than the sensible max of entries for any one City, yet less than the total number of data rows.
So in your example: 30 is a sensible max for any one city and less than the 35 lines of data.

Hope that makes sense?


Book1
ABCD
1CityIDDesired output
2Colognece7c03Uniques for Cologne
3Colognea34d2  
4Colognea34d2  
5Colognea34d2  
6Colognea34d2  
7Cologne38df1  
8Cologne38df1  
9Cologne38df1  
10Portland43aff8Uniques for Portland
11Portland437c3  
12Portland437c4  
13Portland067b2  
14Portland067b3  
15Portlandbc53a  
16Portland437c4  
17Portland437c4  
18Portlandbc53a  
19Portlandbc53a  
20Portland9fd7d  
21Portland4fd7c  
22Portland9fd7d  
23Portland9fd7d  
24Budapeste8ad36Uniques for Budapest
25Budapestb8a2a  
26Budapestb8a2a  
27Budapestb8a2a  
28Budapest8154  
29Budapest8154  
30Budapest96b0f  
31Budapest96b0f  
32Budapestb8a2a  
33Budapest61a33  
34Budapestf9066  
35Budapestf9066  
36  
37  
Sheet1
Cell Formulas
RangeFormula
C2:C37C2=IF(OR(A2="",A1=A2),"",SUMPRODUCT(IFERROR(1/COUNTIF(B2:B30,B2:B30),0)*(B2:B30 = B2:B30)*(A2:A30=A2)))
D2:D37D2=IF(C2="","","Uniques for " & A2)
 
Upvote 0
Snakehips said:
@boris1 The below is not the prettiest formula but maybe it will help?
Note that the rows in my starting formula are 2:30
You should use 2 for the start row.
I have used 30 for the end row.
You need to use ? where ? is more than the sensible max of entries for any one City, yet less than the total number of data rows.
So in your example: 30 is a sensible max for any one city and less than the 35 lines of data.

Hope that makes sense?


Book1
ABCD
1CityIDDesired output
2Colognece7c03Uniques for Cologne
3Colognea34d2  
4Colognea34d2  
5Colognea34d2  
6Colognea34d2  
7Cologne38df1  
8Cologne38df1  
9Cologne38df1  
10Portland43aff8Uniques for Portland
11Portland437c3  
12Portland437c4  
13Portland067b2  
14Portland067b3  
15Portlandbc53a  
16Portland437c4  
17Portland437c4  
18Portlandbc53a  
19Portlandbc53a  
20Portland9fd7d  
21Portland4fd7c  
22Portland9fd7d  
23Portland9fd7d  
24Budapeste8ad36Uniques for Budapest
25Budapestb8a2a  
26Budapestb8a2a  
27Budapestb8a2a  
28Budapest8154  
29Budapest8154  
30Budapest96b0f  
31Budapest96b0f  
32Budapestb8a2a  
33Budapest61a33  
34Budapestf9066  
35Budapestf9066  
36  
37  
Sheet1
Cell Formulas
RangeFormula
C2:C37C2=IF(OR(A2="",A1=A2),"",SUMPRODUCT(IFERROR(1/COUNTIF(B2:B30,B2:B30),0)*(B2:B30 = B2:B30)*(A2:A30=A2)))
D2:D37D2=IF(C2="","","Uniques for " & A2)
Click to expand...
Thank you. I’ve found one more perhaps simpler alternative:

=IF(COUNTIFS($C$2:C2,C2,$B$2:B2,B2)>1,0,1)
 
Upvote 0
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
boris1 said:
I’ve found one more perhaps simpler alternative:

=IF(COUNTIFS($C$2:C2,C2,$B$2:B2,B2)>1,0,1)
Click to expand...
Slightly simpler again is shown in column D, or if you only wanted the unique count as per your original request, column E

22 06 04.xlsm
BCDE
1CityID
2Colognece7c013
3Colognea34d21 
4Colognea34d20 
5Colognea34d20 
6Colognea34d20 
7Cologne38df11 
8Cologne38df10 
9Cologne38df10 
10Portland43aff18
11Portland437c31 
12Portland437c41 
13Portland067b21 
14Portland067b31 
15Portlandbc53a1 
16Portland437c40 
17Portland437c40 
18Portlandbc53a0 
19Portlandbc53a0 
20Portland9fd7d1 
21Portland4fd7c1 
22Portland9fd7d0 
23Portland9fd7d0 
24Budapeste8ad316
25Budapestb8a2a1 
26Budapestb8a2a0 
27Budapestb8a2a0 
28Budapest81541 
29Budapest81540 
30Budapest96b0f1 
31Budapest96b0f0 
32Budapestb8a2a0 
33Budapest61a331 
34Budapestf90661 
35Budapestf90660 
Count Unique
Cell Formulas
RangeFormula
D2:D35D2=--(COUNTIFS(B$2:B2,B2,C$2:C2,C2)=1)
E2:E35E2=IF(B2=B1,"",ROWS(UNIQUE(FILTER(C$2:C$35,B$2:B$35=B2))))
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

PhBarreto
  • Solved
Help WITH SUMPRODUCT
2
Replies
10
Views
161
Fluff
Fluff
drom
  • Solved
Count unique values for filtered column
Replies
1
Views
2K
myall_blues
myall_blues
K
  • Solved
Count number of max values per unique value and column heading?
Replies
5
Views
291
RobP
R
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
357
MARZIOTULLIO
M
R
  • Solved
Count Unique Values excluding blanks and one value
Replies
6
Views
158
Domenic
D

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top