countif text formula help

[JCH]

How can count the number of times part of person's name (either first or
last) in cell C84 appears in the range C8499? The following formula
doesn't do the trick. Any suggestions?

=COUNTIF(C8499,"C65")

 

[Biff]

Hi!

Try this:

=SUMPRODUCT((C84:C99={"John","Smith"})+(D8499={"John","Smith"}))

Biff

 

[duane]

how about this?

=COUNTA($c84:$d$99)-SUMPRODUCT((ISERROR(SEARCH(C65,$c$84:$d$99,1)))*1)+COUNTBLANK($C$84:$D$99)