Count Uniques with String Matching

PedalPusher, Good evening.

Try to use:

=COUNTIFS(A2:A10,"John",B2:B10,"Smith",C2:C10,"MA",D2:D10,"*SALEM*")

Is that what you're looking for?
I hope it helps.
-------------
Belo Horizonte, Brazil
Marcílio Lobão

If I understand what you want I think the correct count is 3.

Data Range

	A	B	C	D	E	F	G	H
1	FIRST	LAST	STATE	CITIES		Smith	Salem	3
2	John	Jones	CT	Salem, Hartford	------	------	------	------
3	Sam	Jones	CT	Hartford
4	Sam	Smith	CT	Hartford
5	John	Smith	CT	Salem
6	Sam	Jones	MA	Salem
7	John	Smith	MA	Boston, Salem
8	John	Smith	MA	Salem, Boston, Concord
9	Sam	Smith	MA	Salem
10	John	Smith	MA	Concord, Salem

This array formula** entered in H1:

=SUM(IF(FREQUENCY(IF((B2:B10=F1)*ISNUMBER(SEARCH(G1,D2:D10)),MATCH(A2:A10&"_"&B2:B10&"_"&C2:C10,A2:A10&"_"&B2:B10&"_"&C2:C10,0)),ROW(D2:D10)-ROW(D2)+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.

Rows being counted are 5, 7 and 9.