countif text formula help

JCH · Jun 27, 2005

How can count the number of times part of person's name (either first or
last) in cell C84 appears in the range C84

99? The following formula
doesn't do the trick. Any suggestions?

=COUNTIF(C84

99,"C65")

Biff · Jun 28, 2005

Hi!

Try this:

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

99={"John","Smith"}))

Biff

duane · Jun 28, 2005

how about this?

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