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 D2 a formula counts the number of times the searchstring is found in A1:A15.
=SUM(IF(ISNUMBER(FIND(D1,A1:A15)),1,0)) + Ctrl + Shift + Enter

In E2:E13 i get the row number of the cell which the search string is found.
=IF(SUM(IF(ISNUMBER(FIND(D1,A1:A15)),1,0))>=ROW()-1, SMALL(IF(ISNUMBER(FIND(D1,A1:A15)),ROW(A1:A15),""),ROW()-1),"") + Ctrl + Shift + Enter

In F2:F13 is the cell value displayed.
=IF(E2<>"", INDEX($A$1:$A$15, E2), "") copied down to F13.

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

Download excel sample file for this tutorial.
string-exist-in-multiple-cells
(Excel 97-2003 Workbook *.xls)

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:

1. Lookup with multiple criteria and display multiple search results using excel formula, part 4
2. Filter unique distinct values where adjacent cells contain search string in excel