Need formula to count unique projects for any given Project Manager

[Carlos Ruano]

Hi, I need a formula that will give me the count of projects that a particular Project Manager is assigned to.

A simplified example of my file is:

A2:A10 contains Sponsor names (ie. GSK, Merck, Biogen, Novartis, ect..)
B2:B10 contains Project Managers names who are assigned to the projects
C2 - A macro on this sheet causes a project manager's name to populate C2 based on a certain action. So, for the purpose of this example, let's just say that any of the Project Manager's names could be in C2 at any given time.
D2 - This is where I need your help! I want a formula that will look at the Project Manager's name in C2, and then tell me how many unique projects she is assigned to.

As implied by the above example, and my needs, Project Names might be repeated multiple times in column A, and Project Manager's names might be repeated multiple times in column B. So, if Susy Dunkin currently appears in C2, and her name appears three times in column B, but her name appears next to GSK twice and Merck once, then the result that I want to see in D2 is 2. She is only assigned to two unique sponsors.

Please let me know if any additional information is needed.

Thank you!
Carlos

 

[vletm]

a Sample File

 

[Carlos Ruano]

a Sample File

File uploaded to original post.

 

[John Jairo V]

Hi, to both of you!

This could be an option (Array Formula, must be entered with CSE - Ctrl + Shift + Enter - , and not just Enter)

[D2] : =SUM(N(FREQUENCY(IF(B2:B69=C2,MATCH(A2:A69,A2:A69,)),ROW(A2:A69)-ROW(A1))>0))

Blessings!

 

[vletm]

Carlos Ruano
Would You do it this way?
or even with 'drop down' then no need to write name?

 

[Carlos Ruano]

Hi, to both of you!

This could be an option (Array Formula, must be entered with CSE - Ctrl + Shift + Enter - , and not just Enter)

[D2] : =SUM(N(FREQUENCY(IF(B2:B69=C2,MATCH(A2:A69,A2:A69,)),ROW(A2:A69)-ROW(A1))>0))

Blessings!

Thank you, John! This seems to be working perfectly.

 

[Carlos Ruano]

Carlos Ruano
Would You do it this way?
or even with 'drop down' then no need to write name?

Hi vletm,

Thank you for what you provided. Your solution is fine; however, the sample file that I provided is not exactly like the real file. I would liked to have provided the actual file that I am working with, but if I did, my company would probably fire me (sensitive material)!

The drop down for name choosing is not necessary, because the name is automatically populated in C2 via a macro that is in the original file (not the sample I provided). So, we don't actually need to populate C2 with the Project Manager's name manually in the real file, only in the sample file.

 

[vletm]

Carlos Ruano -- the sample file that I provided is not exactly like the real file.
Sometimes it would be easier for You
if the sample file even looks like the real file.
If no need to see all values in same time then - okay.