using sumproduct with containing a word

[johnjohn5]

Hi i need to some help with sumproduct. what i need is a way to use sumproduct with a fuction that contains a word. this is an example but doesn't work. =SUMPRODUCT(B2:B17,--($A2:$A17= contains "john")).
my problem is i have a sheet with names on it and some times people enter whole names and some times they dont. i need a way to sum up a number even if the name is "John S." or "S, John". Thanks

Thanks for your help that worked.

[VBA Noob]

Maybe

=SUMPRODUCT((ISNUMBER(SEARCH("john",$A2:$A17))*(B2:B17)))

See link for more tips for sumproduct

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VBA Noob