Modified your formula a little and it's done --
Formula:
=SUMPRODUCT((LEN(IF($E$2:$E$8001=$K$2,$F$2:$F$8001))-LEN(SUBSTITUTE(UPPER(IF($E$2:$E$8001=$K$2,$F$2:$F$8001)),UPPER("ist"),"")))/LEN("ist")+ (LEN(IF($E$2:$E$8001=$K$2,$F$2:$F$8001))-LEN(SUBSTITUTE(UPPER(IF($E$2:$E$8001=$K$2,$F$2:$F$8001)),UPPER("kl"),"")))/LEN("kl"))
Confirm with Ctrl+Shift+Enter
Two more formulae, little smaller ones--
Formula:
=SUM((LEN(IF($E$2:$E$8001=$K$2,$F$2:$F$8001))-LEN(SUBSTITUTE(LOWER(IF($E$2:$E$8001=$K$2,$F$2:$F$8001)),{"kl","ist"},"")))/LEN({"kl","ist"}))
Formula:
=SUM((LEN(IF($E$2:$E$8001=$K$2,$F$2:$F$8001))-LEN(SUBSTITUTE(LOWER(IF($E$2:$E$8001=$K$2,$F$2:$F$8001)),"kl","")))/LEN("kl"))+SUM((LEN(IF($E$2:$E$8001=$K$2,$F$2:$F$8001))-LEN(SUBSTITUTE(LOWER(IF($E$2:$E$8001=$K$2,$F$2:$F$8001)),"ist","")))/LEN("ist"))
Confirm with Ctrl+Shift+Enter
Please hit the "Add Reputation" button if you liked the answer
Bookmarks