count unique values based on 1 condition

Try this...

Data Range

	A	B	C	D	E
1	Client	Subject	------	Subject	Count
2	A	XXX		PPP	2
3	A	XXX		XXX	3
4	A	ZZZ		ZZZ	1
5	B	PPP		RRR	1
6	C	PPP
7	C	XXX
8	D	RRR
9	D	XXX
10	D	RRR

This array formula** entered in E2 and copied down:

=SUM(IF(FREQUENCY(IF(B$2:B$10=D2,MATCH(A$2:A$10,A$2:A$10,0)),ROW(B$2:B$10)-ROW(B$2)+1),1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

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

According to their profile, they're using Excel 2003.

COUNTIFS is not available in that version.

The SUM/IF/FREQUENCY array formula is significantly faster to calculate compared to the SUMPRODUCT/COUNTIFS formula.

On large ranges the difference is huge.

Also, in the array formula using MATCH/ROW is faster to calculate compared to MATCH/MATCH.