Countifs on TEXT

Y

ycwkpp898

hi i need to do a countifs to tell me how many times someone said Happy (
sometime they will put something like bob says: Happy chappy
Bob says: Happy
George says: Sad
Bob says: Happy man
Helen says: Happyyyyyyyyyy

each name and writing is in one cell
I want it to go to each name and put next to there name the amount of times
they said Happy i have tried the following

=IF(F4=""," ",COUNTIFS(alias_played,F4,who_played,">=Happy"))
also how can i put so if some people put Happy, HAPPY, Happyyyyyyyyy
 
S

Stefi

Text cells being in A1:A4, list of names in C1:C3, enter this formula in D1
and copy down to D3!
=SUMPRODUCT(--(NOT(ISERROR(SEARCH(C3,$A$1:$A$4)))),--(NOT(ISERROR(SEARCH("happy",$A$1:$A$4)))))
Adjust ranges to real table. It is case insensitive, if you need a case
sensitive search replace SEARCH by FIND function!

Regards,
Stefi

„ycwkpp898†ezt írta:
 
Top