Count number of times a string exist in multiple cells using excel formula
Question: How do I find the number of occurances a word exists in a range of cells? It does not have to be a exact match but case sensitive. Column A1:A15 is the cell range.
Answer:
Cell D1 is the search string. In cell D2 an array formula counts the number of times the search string is found in cell range A1:A15.
Case sensitive array formula in cell D2:
Case insensitive array formula in cell D2:
How to create an array formula
- Select cell D2
- Copy and paste above array formula to formula bar
- Press and hold Ctrl + Shift
- Press Enter
Array formula in cell E2:
Not case-sensitive
Replace FIND with SEARCH in the above formula to make it not case sensitive.
Download *.xlsx file
string-exist-in-multiple-cellsv2.xlsx
(Excel Workbook *.xlsx)
Functions used in this article
IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range
ROW(reference) returns the rownumber of a reference
COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers
SMALL(array,k) returns the k-th smallest row number in this data set.
ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE
This blog article is one out of three articles on the same subject.
- Count number of times a string exist in multiple cells using excel formula
- Count text that occurs multiple times in excel cell
- Count occurences of a specific text string in a column in excel
Read more related articles in the archive.
Related posts:
Count text that occurs multiple times in excel cell
Excel: Count the number of cells within a range that meet the given condition
Excel: Count the number of occurances an integer is in a list
Filter unique distinct values where adjacent cells contain search string in excel
Recommended categories
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: http://www.get-digital-help.com/2009/12/29/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.
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, "")