Frequency of unique values based on a single criteria

[pedexe90]

Hi there,

I am trying to find the number of non-repeated (unique/frequency) family names in each grade. I have prepared a sample and uploaded to one drive. I would appreciate it if someone could help?

https://1drv.ms/x/s!AgQdYsSdg735fXy_R4rJuVlmcuw

 

[Nishant94]

Is this what you are looking for?

=SUMPRODUCT((FREQUENCY(MATCH($D$8:$D$45,$D$8:$D$45,0),ROW($D$8:$D$45)-ROW($D$8)+1)>0)*($G$8:$G$46=J9))

You sample doesn't seem to have any family name that is repeating.

 

[Aladin Akyurek]

In K9 control+shift+enter, not just enter, and copy down:

=SUM(IF(FREQUENCY(IF(1-($D$8:$D$45=""),IF($G$8:$G$45=$J9,MATCH($D$8:$D$45,$D$8:$D$45,0))),ROW($D$8:$D$45)-ROW($D$8)+1),1))