macro/formula to count distinct resources and match to location

Hi

i am looking to create a small table from the attached worksheet that will show how many users there are for 3 different locations, the user names are in column a and the locations in column c.

the thing is, the actual worksheet i am using in work contains almost 1000 lines, and is being updated daily, there can be multiple entries for a person for projects etc.. and someone can even be in the availability and pto area's without being in the main project area..
each resource can only be on one location so that will not change.

also, there can be blank cells for resource which should not be counted.

i was wondering if there was a formula or a macro that would count the distinct number of names and reference them to the location and give a count of resources by location?

does anyone have any suggestions?
the actual worksheet is huge, and has lots of different sections, but the columns are the same so the distinct of names matched to the 3rd column would work for the whole sheet.

Many Thanks
Neill