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:

=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.

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.

Read more related articles in the archive.