3

I have some cells with text. I need to count the occurrences of a specific word (not a list) from those cells.

Example sheet:https://docs.google.com/spreadsheets/d/1WECDbepLtZNwNfUmjxfKlCbLJgjyUBB72yvWDMzDBB0/edit?usp=sharing

So far I found one way to count it in English by using SUBSTITUTE to replace all these words with "":

=(LEN(B1)-LEN(SUBSTITUTE(UPPER(B1),UPPER(A5),"")))/LEN(A5)

However, I don't know why but it doesn't work in German.

Edited: I don't want to count "Hero" in "Heroes". However, I'd like to count "afk" in "AFK-Spiel" (German for example). Is it possible?

JOY
  • 399
  • 3
  • 15
  • Does this answer your question? [Check if cell contains keywords from another cell - Google Sheets](https://stackoverflow.com/questions/51620202/check-if-cell-contains-keywords-from-another-cell-google-sheets) – Umar.H May 22 '20 at 23:24
  • No it doesn't please take a look at my demo sheet. – JOY May 23 '20 at 02:53

3 Answers3

4

If you want to count occurences of "Hero" word

=COUNTIF(SPLIT(JOIN(" ", B1:B3), " -."&CHAR(10)), "Hero")

Where:

  • B1:B3: cells with text
  • "Hero": the word to count

Explaination

  1. JOIN(" ", B1:B3): Concatenation of all cells with text
  2. SPLIT(..., " -."&CHAR(10)): Create an array with each words
  3. COUNTIF(..., "Hero"): Count each array item equals to "Hero"

Example

if input text is: 

Hero Hero-666 heroes heroic

➔ then formula will return 2.

If you want to count occurences of "Hero" string

(Even nested in an other word, i.e: "Heroes")

=COUNTA(SPLIT(UPPER(JOIN(" ",B1:B3)), "HERO", false, false))-1

Where:

  • B1:B3: cells with text
  • "HERO": the string to count

Explaination

  1. JOIN(" ", B1:B3): Concatenation of all cells with text
  2. UPPER(...): Convert text in upper case
  3. SPLIT(..., "HERO"): Split on each occurences of the string
  4. COUNTA(...)-1: Count how many splits have been done

Example

if input text is: 

Hero Hero-666 heroes heroic

➔ then formula will return 4.

Tom
  • 677
  • 7
  • 21
  • It seems that the result is not correct. It should be 14, not 6. – JOY May 23 '20 at 07:09
  • @JOY, I've added a solution to count all occurences of a string. I hope it fits your need. – Tom May 23 '20 at 14:55
  • I don't want to count "Heroes" the same as "Hero". Instead, "hero", "HERO", "Hero" are count as the same. – JOY May 23 '20 at 20:15
  • 1
    There is one more issue: This doesn't count "afk" in "AFK-Spiel". I need it to be counted while "afk" in "afkxxx" is not. Is it possible? – JOY May 23 '20 at 20:33
  • @JOY, I've updated first formula to support that case – Tom May 23 '20 at 21:46
  • Sorry I meant I don't want to count hero in "heroes" or "heroic" but I want to count in "hero-666". – JOY May 24 '20 at 13:22
1

In your sheet you mention that the count should be 14.
Considering that, I believe you are looking for a solution to also include words like heroes or Hero

If you want to include variations of hero, like Hero or Heroes you can use the following:

Case insensitive for any language formula: 

=COUNTIF(SPLIT(CONCATENATE(B1:B3), " "), "*heRO*")

You can even have *heRO* placed in a cell like A7 and use

=COUNTIF(SPLIT(CONCATENATE(B1:B3), " "), A7)

If you want just the word Hero, remove the asterisks * around it.

It also works for any language (including German).

marikamitsos
  • 10,264
  • 20
  • 26
  • This works and is similar to @Tom approach. Thank you – JOY May 23 '20 at 20:23
  • *"...is similar to @Tom approach"* Appears very similar. Please try `=COUNTA(SPLIT(UPPER(JOIN(" ",C1:C3)), "für", false, false))-1` with `Für` or `xxxfür` in the text. In any case all right solutions may have variations. Most of the times there are more than just one approaches to an issue. – marikamitsos May 23 '20 at 21:14
  • better use TEXTJOIN to replace CONCATENATE so that we can join them by a specific delimiter, otherwise if you have "heroherohero" ended up in one cell, they'll be counted only once – Zach Sep 09 '22 at 14:16
0

try:

=ARRAYFORMULA(COUNTA(IFERROR(SPLIT(QUERY(SUBSTITUTE(
 UPPER(B1:B3), UPPER(A5), "♦"),,99^99), "♦")))-1)

and for german:

=ARRAYFORMULA(COUNTA(IFERROR(SPLIT(QUERY(SUBSTITUTE(
 UPPER(C1:C3), "HELD", "♦"),,99^99), "♦")))-1)
player0
  • 124,011
  • 12
  • 67
  • 124