Counting unique values with multiple criteria

[j9mcl]

Dear All

I am trying to figure out formulas to calculate how many unique ID's feature for each faculty then by year. The faculty is chosen by a dropdown on cell B1 on tab Summary. All data is on tab Master.

I have tried looking in to the frequency function but from what I have read it doesn't work on text.

Any help would be much appreciated.

Thanks

J9 x

[TMS]

That looks ideal for a Pivot Table.

[j9mcl]

I would normally but I have simplified the data and summary sheet for this purpose. There is a lot more on the real summary sheet than it shows you on the sample..

[Glenn Kennedy]

Is this what you meant???

=SUM(INDEX((Master!$C$4:$C$2874=$B$1)*(Master!$B$4:$B$2874=B$6)/COUNTIFS(Master!$A$4:$A$2874,Master!$A$4:$A$2874&"",Master!$B$4:$B$2874,Master!$B$4:$B$2874&"",Master!$C$4:$C$2874,Master!$C$4:$C$2874&""),0))

NOT checked as ~3000 rows is rather more than the 10-20 we hope to see in a sample sheet (see yellow banner, top of page).

[j9mcl]

Thank you Glenn, worked a treat. Sorry about the number of rows, forgot that bit.

Jx

[Glenn Kennedy]

You're welcome & thanks for the rep.