Count Unique Names per ID

eluebbe

New Member
Joined
Mar 8, 2018
Messages
2
Hey! My boss is running some data analysis and asked if I could help pull the number of unique names per project ID number. I currently have a simple countif function that gives me the number of times a project ID appears in the sheet =countif(A:A,A728). This results in 18 people showing on this project. The problem is sometimes the same person could be entered into the project more than once. I can easily figure out how many times that person appears by using =countifs(A:A,A728,I:I,I728,J:J,J728) (searching project ID, FirstName, LastName). This results in 3 (Juan is entered on this form three).

In the scenario given above, there are actually 6 people entered on this project, each one is entered into this project ID thrice. How would I write a formula that would result in the number of unique names assigned to a given project ID (in this case 6)?
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Welcome to Mr. Excel.
It would help those trying to help you if you change your profile to indicate what version of Excel you run and use XL2BB to show an example of what you have and need.
 
Upvote 0
Welcome to the forum!

Try:

Book1
ABCDEF
1ProjectPersonProjectUnique countUnique count (365)
21a166
31b
41c
51d
61e
71f
81a
91b
101c
111d
121e
131f
141a
151b
161c
171f
181e
191d
202
212
22
Sheet3
Cell Formulas
RangeFormula
E2E2=SUM(SIGN(FREQUENCY(IF(A2:A25=D2,MATCH(B2:B25,B2:B25,0)),ROW(A2:A25)-ROW(A2)+1)))
F2F2=ROWS(UNIQUE(FILTER(B2:B25,A2:A25=D2)))
Press CTRL+SHIFT+ENTER to enter array formulas.


If you have Excel 365 try the F2 formula. If an older version, try E2.
 
Upvote 0
Solution
PQ solution:
Book1
ABCDE
1ProjectPersonProjectCount
21a1a, b, c, d, e, f
31b2z, x, c, a, s, d
41c
51d
61e
71f
81a
91b
101c
111d
121e
131f
141a
151b
161c
171f
181e
191d
202z
212x
222c
232a
242s
252d
262z
272c
282x
292a
302s
312d
Sheet1

Power Query:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Project"}, {{"Count", each Text.Combine(List.Distinct([Person]),", "), type text}})
in
    #"Grouped Rows"
 
Upvote 0
Welcome to the forum!

Try:

Book1
ABCDEF
1ProjectPersonProjectUnique countUnique count (365)
21a166
31b
41c
51d
61e
71f
81a
91b
101c
111d
121e
131f
141a
151b
161c
171f
181e
191d
202
212
22
Sheet3
Cell Formulas
RangeFormula
E2E2=SUM(SIGN(FREQUENCY(IF(A2:A25=D2,MATCH(B2:B25,B2:B25,0)),ROW(A2:A25)-ROW(A2)+1)))
F2F2=ROWS(UNIQUE(FILTER(B2:B25,A2:A25=D2)))
Press CTRL+SHIFT+ENTER to enter array formulas.


If you have Excel 365 try the F2 formula. If an older version, try E2.
Thank you! This is exactly what I needed! F2 did the trick. Cheers!
 
Upvote 0

Forum statistics

Threads
1,212,928
Messages
6,110,734
Members
448,294
Latest member
jmjmjmjmjmjm

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