You can use REGEXMATCH:

=IF(REGEXMATCH(A1, "sites"), 1, 0)

To explain, REGEXMATCH returns true if and only if the argument is a substring of your string.

You can use RegExMatch:

=IF(RegExMatch(A1;"Bla");"YES";"NO")

It will likely have been solved by now, but I ran accross this and figured to give my input

=COUNTIF(a2:a51;"*iPad*")

The important thing is that separating parameters in google docs is using a ; and not a ,