Count how many times a string exists in a cell range (case insensitive)
Question: How do I count how many times a word exists in a range of cells? It does not have to be an exact match but case sensitive. Column A1:A15 is the cell range.
Answer:
Cell E2 is the search string. In cell E3 an array formula counts the number of times the search string is found in cell range A1:A15.
Case sensitive formula in cell E3:
Explaining formula in cell E3
Step 1 - Count characters in each cell
The LEN function counts characters in a cell.
LEN(B2:B16)
becomes
LEN({"BBA"; "CAC"; "BBAABBAA"; "DADA"; "EDA"; "DAA"; "BABB"; "TTAADT"; "VADTE"; "ADTE"; "ADADAA"; "DEEB"; "BADT"; "CCDDBB"; "BBCB"})
and returns
{3;3;8;4;3;3;4;6;5;4;6;4;4;6;4}
Step 2 - Substitue search string with nothing in all cells
The SUBSTITUTE function lets you replace a text string with another text string in a cell value or cell range.
SUBSTITUTE($B$2:$B$16, $E$2, "")
becomes
SUBSTITUTE({"BBA"; "CAC"; "BBAABBAA"; "DADA"; "EDA"; "DAA"; "BABB"; "TTAADT"; "VADTE"; "ADTE"; "ADADAA"; "DEEB"; "BADT"; "CCDDBB"; "BBCB"}, "AA", "")
and returns
{"BBA";"CAC";"BBBB";"DADA";"EDA";"D";"BABB";"TTDT";"VADTE";"ADTE";"ADAD";"DEEB";"BADT";"CCDDBB";"BBCB"}
Step 3 - Count characters in array
LEN(SUBSTITUTE($B$2:$B$16, $E$2, ""))
becomes
LEN({"BBA";"CAC";"BBBB";"DADA";"EDA";"D";"BABB";"TTDT";"VADTE";"ADTE";"ADAD";"DEEB";"BADT";"CCDDBB";"BBCB"})
and returns
{3;3;4;4;3;1;4;4;5;4;4;4;4;6;4}
Step 4 - Subtract arrays
LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, ""))
becomes
{3;3;8;4;3;3;4;6;5;4;6;4;4;6;4}-{3;3;4;4;3;1;4;4;5;4;4;4;4;6;4}
and returns
{0;0;4;0;0;2;0;2;0;0;2;0;0;0;0}
Step 5 - Divide with cell length
(LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, "")))/LEN($E$2
becomes
{0;0;4;0;0;2;0;2;0;0;2;0;0;0;0}/LEN($E$2)
becomes
{0;0;4;0;0;2;0;2;0;0;2;0;0;0;0}/{3;3;8;4;3;3;4;6;5;4;6;4;4;6;4}
and returns
{0;0;0.5;0;0;0.666666666666667;0;0.333333333333333;0;0;0.333333333333333;0;0;0;0}
Step 6 - Sum number sin array
The SUMPRODUCT is better in this case because you are not required to enter the formula as an array formula to do the calculations.
SUMPRODUCT((LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, "")))/LEN($E$2))
becomes
SUMPRODUCT({0;0;0.5;0;0;0.666666666666667;0;0.333333333333333;0;0;0.333333333333333;0;0;0;0})
and returns 5 in cell E2.
Case insensitive formula in cell E3:
Array formula in cell E6:
Get *.xlsx file
string exist in multiple cells.xlsx
Count category
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
Question: How do I count the number of times a text string exists in a column? The text string may […]
The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]
Excel categories
23 Responses to “Count how many times a string exists in a cell range (case insensitive)”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Awesome. It is simply superb
How about:
=SUM(N(ISNUMBER(FIND(D1,A1:A15))))
David Hager,
Yes, your formula works!
Thanks for commenting.
Hello. I used this formula and is very useful. But how will look formula to search the exact string (an not only string who include) in column A? Also if i have a value in column B named price, i want to return the value from columb B associated to row of string searched in column A. How to make tthis? Thank you.
Adriano,
Read this post: https://www.get-digital-help.com/vlookup-with-2-or-more-lookup-criteria-and-return-multiple-matches-in-excel/
Hi,
this formula is working only the data where in columns (A1:A15), i required the formula of the values in rows(A1:AZ1)
please help....
Cheran,
Try
Remember, it is an array formula.
Excellent!! But may I know is there is way I can highlight the cells
Prashant
Yes, try this CF formula:
=FIND($D$1,A1)
I have a HUGE list at the moment, and the formula stops working when changing the $A$1:$A$15 to $A$1:$A$8348. Here's what my formula looks like:
=(SUM(LEN(A1:A8348))-SUM(LEN(SUBSTITUTE($A$1:$A$8348,$D$1,""))))/LEN($D$1)
What am I doing wrong?
Haval,
did you create an array formula?
You know if you examine the formula in the formula bar. The formula is surrounded by curly brackets: {=(SUM(LEN(A1:A8348))-SUM(LEN(SUBSTITUTE($A$1:$A$8348,$D$1,""))))/LEN($D$1)}
It is good but now is case sensitive. How to do other away ?
Bob,
I have added a case insensitive formula to this post.
Thanks for commenting!
[...] Count2 Formulas to count the occurrences of text, characters, or words in Excel for Mac Count3 Count number of times a string exist in multiple cells using excel formula | Get Digital Help - Micr... I hope this resolves the problem for you, if not then I am sorry but I cannot help you further [...]
Dear Oscar,
Can it be possible ,Text of one cell filled up to others with the refference of number value entered in a other cell.i.e
A B
1 APPLE 5
Then
A B
1 APPLE 5
2 APPLE
3 APPLE
4 APPLE
5 APPLE
Amit,
I am not sure I understand.
=IF(COUNTIF($A$1:A1, $A$1:A1)<$B$1, A1, "")
How could you do this so it only counts the EXACT searchstring (so it would find AA but not AAB). I need to do this or something similar to count how many times certain numbers appear. The same number could appear in the same cell more than once. However, if i am searching for the number "1" i do not want it to also count "10" or "11".
This works! Thank you very much!!!
Dear Oscar,
I have a string in a cell as follows:
MKS3PIN-5 DC24 with PF113A-E & PFC-A1. These are model numbers of a products.
I have a list containing these and much more. "With" is not a product.
MKS3PIN-5 DC24 is a single product even though there is a space before DC24.
I want to extract the model numbers only in adjacent columns and receive a message stating that all models have been extracted. Also to say which model number is not found in the string.
The main purpose of this exercise is to arrive at the combined prices of the above combination.
Thanks & Regards
S.Narasimhan
I want to use the above formula on filtered rows. How can I do that? Any help is appreciated. Thanks!
Dear Oscar,
This is a very helpful formula. My requirement is to use the exact formula but on filtered rows so the rows can change based on the filter condition. Is there an easy fix for this? Any pointers will be helpful. Thanks.
Paras Desai,
great question.
Array formula in cell C3:
=SUM((LEN(IF(SUBTOTAL(103, OFFSET(Table1[Country], MATCH(ROW(Table1[Country]), ROW(Table1[Country]))-1, 0, 1)), Table1[Text]))-LEN(SUBSTITUTE(IF(SUBTOTAL(103, OFFSET(Table1[Country], MATCH(ROW(Table1[Country]), ROW(Table1[Country]))-1, 0, 1)), Table1[Text]), $C$2, "")))/LEN($C$2))
Get the workbook
Count-string-in-a-filtered-table.xlsx
Paras Desai,
You also have the option to let the Excel defined Table do the math.
Formula in cell D5:
=(LEN([@Text])-LEN(SUBSTITUTE([@Text], $C$2, "")))/LEN($C$2)
1. Select any cell in the Excel defined Table.
2. Go to tab Table design.
3. Press with left mouse button on check box "Total row" to show the total.