Forum Discussion

Peter_Dankmeijer's avatar
Peter_Dankmeijer
Copper Contributor
Jan 06, 2019

Counting words in cells with multiple words

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("ap...
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 07, 2019

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/

 

Resources