count unique values based on 1 condition

Or this

in D2

=SUMPRODUCT((B1:B6=D1)/COUNTIFS(A1:A6,A1:A6,B1:B6,B1:B6&""))


For Excel 2003 use this array formula

=SUM(IF(FREQUENCY(IF((B$1:B$6=D1),MATCH(A$1:A$6,A$1:A$6,0)),MATCH(B$1:B$6,B$1:B$6,0))>0,1))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

Row\Col	A	B	C	D
1	Steve	Subject1		Subject1
2	John	Subject2		2
3	Steve	Subject1
4	Andrew	Subject3
5	Steve	Subject2
6	John	Subject1