Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Counting the number of times a word appears in a worksheet
Hi,
I want to have a formula in a cell that counts the number of time a certain word appears in any cell in a worksheet. i've tried using count but it only counts for numbers, not text. Thanks, Jignesh. |
#2
|
|||
|
|||
Jig Bhakta wrote...
I want to have a formula in a cell that counts the number of time a certain word appears in any cell in a worksheet. i've tried using count but it only counts for numbers, not text. If you want to count cells containing a particular substring, so if your substring were "XYZ" and cell X99 contained "123XYZ456XYZ789" cell XYZ would count as one match, use COUNTIF, e.g., =COUNTIF(Range,"*"&<YourSubstringHere&"*") If you want to count every instance including multiple instances in the same cell as separate matches, use =SUMPRODUCT((LEN(Range)-LEN(SUBSTITUTE(Range,<YourSubstringHere,"")) /LEN(<YourSubstringHere)) |
#3
|
|||
|
|||
One way
=SUMPRODUCT((LEN(A1:F30)-(LEN(SUBSTITUTE(A1:F30,"word",""))))/LEN("word")) Regards, Peo Sjoblom "Jig Bhakta" wrote in message ... Hi, I want to have a formula in a cell that counts the number of time a certain word appears in any cell in a worksheet. i've tried using count but it only counts for numbers, not text. Thanks, Jignesh. |
#4
|
|||
|
|||
Missed a bracket
=SUMPRODUCT((LEN(Range)-LEN(SUBSTITUTE(Range,<YourSubstringHere,""))) /LEN(<YourSubstringHere)) "Harlan Grove" wrote in message oups.com... Jig Bhakta wrote... I want to have a formula in a cell that counts the number of time a certain word appears in any cell in a worksheet. i've tried using count but it only counts for numbers, not text. If you want to count cells containing a particular substring, so if your substring were "XYZ" and cell X99 contained "123XYZ456XYZ789" cell XYZ would count as one match, use COUNTIF, e.g., =COUNTIF(Range,"*"&<YourSubstringHere&"*") If you want to count every instance including multiple instances in the same cell as separate matches, use =SUMPRODUCT((LEN(Range)-LEN(SUBSTITUTE(Range,<YourSubstringHere,"")) /LEN(<YourSubstringHere)) |
#5
|
|||
|
|||
The formula given by Peo worked....
Thanks. "Peo Sjoblom" wrote: One way =SUMPRODUCT((LEN(A1:F30)-(LEN(SUBSTITUTE(A1:F30,"word",""))))/LEN("word")) Regards, Peo Sjoblom "Jig Bhakta" wrote in message ... Hi, I want to have a formula in a cell that counts the number of time a certain word appears in any cell in a worksheet. i've tried using count but it only counts for numbers, not text. Thanks, Jignesh. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i link a number of worksheets to one master worksheet? | Excel Worksheet Functions | |||
Weekly Transaction Processing | Excel Worksheet Functions | |||
Inserting worksheet in Word | Excel Discussion (Misc queries) | |||
Whats the function to count the total times a word is displayed | Excel Discussion (Misc queries) | |||
Can the number of times undo is used in Excel 2002 be increased? | Setting up and Configuration of Excel |