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.

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.

Read more related articles in the archive.