Home

Counting words in cells with multiple words

%3CLINGO-SUB%20id%3D%22lingo-sub-309400%22%20slang%3D%22en-US%22%3ECounting%20words%20in%20cells%20with%20multiple%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309400%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20a%20survey%20analysis%20in%20Excel%2C%20I%20am%20trying%20to%20count%20unique%20words%20in%20cells%20with%20multiple%20text%20values%20per%20cell.%20I%20found%20the%20formula%20%3DSOM(LENGTE(A2%3AA7)-LENGTE(SUBSTITUEREN(A2%3AA7%3B%22apple%22%3B%22%22)))%2FLENGTE(%22apple%22)%20(example%20to%20select%20%22apple%22%20in%20a%20range%20of%20fruit%20names)%20but%20when%20I%20press%20Crtl-Shift-Enter%2C%20nothing%20happens%20and%20the%20formula%20gives%20a%20%23value%20mistake.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20my%20own%20research%20file%2C%20I%20uswed%20the%20variation%20%3DSOM(LENGTE(GD10%3AGD10000)-LENGTE(SUBSTITUEREN(GD10%3AGD10000%3B%22HAVO%22%3B%22%22))%2FLENGTE(%22HAVO%22))%20(counting%20the%20word%20%22HAVO%22%2C%20but%20I%20get%20a%20value%20of%2083%20in%20stead%20of%20the%205%20times%20this%20word%20appears%20in%20my%20test%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20explain%20how%20to%20get%20a%20correct%20result%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-309400%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-309604%22%20slang%3D%22en-US%22%3ERe%3A%20Counting%20words%20in%20cells%20with%20multiple%20words%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-309604%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Peter%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFirst%2C%20I'd%20use%20SUMPRODUCT%20to%20avoid%20array%20formulas.%20More%20about%20this%20is%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Fcount-specific-words-in-a-range%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Fcount-specific-words-in-a-range%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESecond%2C%20check%20brackets%20in%20your%20formulas.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThird%2C%20take%20into%20account%20SUBSTITUTE%20is%20case%20sensitive.%3C%2FP%3E%0A%3CP%3ESo%2C%20I'd%20use%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((LEN(A2%3AA7)-LEN(SUBSTITUTE(UPPER(A2%3AA7)%2CUPPER(%22apple%22)%2C%22%22)))%2FLEN(%22apple%22))%0A%0Aand%0A%0A%3DSUMPRODUCT((LEN(GD10%3AGD10000)-LEN(SUBSTITUTE(UPPER(GD10%3AGD10000)%2CUPPER(%22HAVO%22)%2C%22%22)))%2FLEN(%22HAVO%22))%3C%2FPRE%3E%0A%3CP%3EAbove%20formulas%20are%20for%20English%20SKU%2C%20you%20may%20translate%20them%20back%20to%20Dutch%20here%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fen.excel-translator.de%2Ftranslator%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fen.excel-translator.de%2Ftranslator%2F%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
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