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:

=SUM((LEN(A1:A15)-LEN(SUBSTITUTE(\$A\$1:\$A\$15, \$D\$1, "")))/LEN(\$D\$1))

Case insensitive array formula in cell D2:

=SUM((LEN(A1:A15)-LEN(SUBSTITUTE(UPPER(\$A\$1:\$A\$15), UPPER(\$D\$1), "")))/LEN(\$D\$1))

How to create an array formula

1. Select cell D2
2. Copy and paste above array formula to formula bar
3. Press and hold Ctrl + Shift
4. Press Enter

Array formula in cell E2:

=IFERROR(INDEX(\$A\$1:\$A\$15, SMALL(IF(ISNUMBER(FIND(\$D\$1, \$A\$1:\$A\$15)),  MATCH(ROW(A1:A15), ROW(A1:A15)), ""), ROW(A1))), "")

Not case-sensitive
Replace FIND with SEARCH in the above formula to make it not case sensitive.

string-exist-in-multiple-cellsv2.xlsx
(Excel Workbook *.xlsx)

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.

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

Search for multiple text strings in multiple cells in excel