Excel 2007 : Formula with multiple selection criteria that excludes duplicates

If this is my data, I want to know how many unique teachers an Advisor is responsible for. So Dallas Jones would only be responsible for 1 teacher, since Gracie Finknottle is listed twice.
Teacher .....................Advisor
Gracie Finknottle.....Dallas Jones
Gracie Finknottle.....Dallas Jones
Kaye Feeds.............Cynthia Walker
Crissy Jones...........Travis Heights
Diana Mayer............Cynthia Walker
Myron Bertram........Travis Heights
Crissy Jones..........Travis Heights
Erika Newsom........Cynthia Walker

I sure hope there is a way to do this!!

Also, I also made the novice error of posting this in the Microsoft Answers forum too http://social.answers.microsoft.com/...a-0516bd806c28
Thanks, Margaret

One way to go about it.

1. Use Advanced Filter to remove duplicates

2. Use a Pivot Table to get the counts

See attached.

Note: I've shown the Pivot Table on the same sheet as the data. Best practice is to place Pivot Tables on their own worksheet.

I hope you didn't think you could post the exact same question to more than one forum and nobody would notice. You now have at least two pools of talent working on the same problem...and neither is aware of the other. Even worse, one of those forums could still be trying to help you after the other has already solved your problem.

The rules of this forum are very clear. You must post a link to the other forum.
http://social.answers.microsoft.com/...a-0516bd806c28

Just to make it easy, here is the rule you should have read and to which you agreed to follow when you joined the forum.

Rule #8

Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!