I am looking for some Excel related help for a formula which I've not been able to figure out myself.
I have a sample list of data in range A1:B7 - Column A is a list of names (e.g. John, Jane, etc) and column B is a list of project codes (e.g. ABC, ABD, Admin, etc), just like this:
A B
John ABC
Jane ABD
John Admin
Jane ABC
Mike ABD
John ABC
Jane Admin
I want to count the number of unique projects that John and Jane have each worked on but exclude the Admin project.
For example, John has worked twice on project ABC' and Admin, therefore the unique project count for John would be 1.
Jane has worked on projects ABC, ABD and Admin, therefore the unique project count for Jane would be 2.
I've tried all various combinations of sumifs, countifs and pivot tables but not managed to count the unique projects with the exclusion of Admin.