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.
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:
Replace FIND with SEARCH in the above formula to make it not case sensitive.
Download *.xlsx file
(Excel Workbook *.xlsx)
Functions used in this article
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE
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
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.
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.