Counting words in cells with multiple words

[Peter_Dankmeijer]

In a survey analysis in Excel, I am trying to count unique words in cells with multiple text values per cell. I found the formula =SOM(LENGTE(A2:A7)-LENGTE(SUBSTITUEREN(A2:A7;"apple";"")))/LENGTE("apple") (example to select "apple" in a range of fruit names) but when I press Crtl-Shift-Enter, nothing happens and the formula gives a #value mistake.

In my own research file, I uswed the variation =SOM(LENGTE(GD10:GD10000)-LENGTE(SUBSTITUEREN(GD10:GD10000;"HAVO";""))/LENGTE("HAVO")) (counting the word "HAVO", but I get a value of 83 in stead of the 5 times this word appears in my test file.

Can someone explain how to get a correct result?

[#2]

Hi Peter,

First, I'd use SUMPRODUCT to avoid array formulas. More about this is here https://exceljet.net/formula/count-specific-words-in-a-range

Second, check brackets in your formulas.

Third, take into account SUBSTITUTE is case sensitive.

So, I'd use

=SUMPRODUCT((LEN(A2:A7)-LEN(SUBSTITUTE(UPPER(A2:A7),UPPER("apple"),"")))/LEN("apple"))

and

=SUMPRODUCT((LEN(GD10:GD10000)-LEN(SUBSTITUTE(UPPER(GD10:GD10000),UPPER("HAVO"),"")))/LEN("HAVO"))

Above formulas are for English SKU, you may translate them back to Dutch here https://en.excel-translator.de/translator/