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.
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 | ||||||
---|---|---|---|---|---|---|
A | B | C | D | |||
1 | City | ID | Desired output | |||
2 | Cologne | ce7c0 | 3 | uniques for Cologne | ||
3 | Cologne | a34d2 | ||||
4 | Cologne | a34d2 | ||||
5 | Cologne | a34d2 | ||||
6 | Cologne | a34d2 | ||||
7 | Cologne | 38df1 | ||||
8 | Cologne | 38df1 | ||||
9 | Cologne | 38df1 | ||||
10 | Portland | 43aff | 8 | Uniques for Portland | ||
11 | Portland | 437c3 | ||||
12 | Portland | 437c4 | ||||
13 | Portland | 067b2 | ||||
14 | Portland | 067b3 | ||||
15 | Portland | bc53a | ||||
16 | Portland | 437c4 | ||||
17 | Portland | 437c4 | ||||
18 | Portland | bc53a | ||||
19 | Portland | bc53a | ||||
20 | Portland | 9fd7d | ||||
21 | Portland | 4fd7c | ||||
22 | Portland | 9fd7d | ||||
23 | Portland | 9fd7d | ||||
24 | Budapest | e8ad3 | 6 | Uniques for Budapest | ||
25 | Budapest | b8a2a | ||||
26 | Budapest | b8a2a | ||||
27 | Budapest | b8a2a | ||||
28 | Budapest | 08154 | ||||
29 | Budapest | 08154 | ||||
30 | Budapest | 96b0f | ||||
31 | Budapest | 96b0f | ||||
32 | Budapest | b8a2a | ||||
33 | Budapest | 61a33 | ||||
34 | Budapest | f9066 | ||||
35 | Budapest | f9066 | ||||
36 | ||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2 | C2 | =SUMPRODUCT(1/COUNTIF(B$2:B$9,B$2:B$9)) |
C10 | C10 | =SUMPRODUCT(1/COUNTIF(B$10:B$23,B$10:B$23)) |
C24 | C24 | =SUMPRODUCT(1/COUNTIF(B$24:B$35,B$24:B$35)) |