Home

Counting words in cells with multiple words

Peter_Dankmeijer
Occasional Visitor

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?

1 Reply

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/

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies