Author: Oscar Cronquist Article last updated on November 17, 2018

Question: How do I count how many times a word exists in a range of cells? It does not have to be an exact match but case sensitive. Column A1:A15 is the cell range.

Answer:

Cell E2 is the search string. In cell E3 an array formula counts the number of times the search string is found in cell range A1:A15.

Case sensitive formula in cell E3:

=SUMPRODUCT((LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, "")))/LEN($E$2))

Explaining formula in cell E3

Step 1 - Count characters in each cell

The LEN function counts characters in a cell.

LEN(B2:B16)

becomes

LEN({"BBA"; "CAC"; "BBAABBAA"; "DADA"; "EDA"; "DAA"; "BABB"; "TTAADT"; "VADTE"; "ADTE"; "ADADAA"; "DEEB"; "BADT"; "CCDDBB"; "BBCB"})

and returns

{3;3;8;4;3;3;4;6;5;4;6;4;4;6;4}

Step 2 - Substitue search string with nothing in all cells

The SUBSTITUTE function lets you replace a text string with another text string in a cell value or cell range.

SUBSTITUTE($B$2:$B$16, $E$2, "")

becomes

SUBSTITUTE({"BBA"; "CAC"; "BBAABBAA"; "DADA"; "EDA"; "DAA"; "BABB"; "TTAADT"; "VADTE"; "ADTE"; "ADADAA"; "DEEB"; "BADT"; "CCDDBB"; "BBCB"}, "AA", "")

and returns

{"BBA";"CAC";"BBBB";"DADA";"EDA";"D";"BABB";"TTDT";"VADTE";"ADTE";"ADAD";"DEEB";"BADT";"CCDDBB";"BBCB"}

Step 3 - Count characters in array

LEN(SUBSTITUTE($B$2:$B$16, $E$2, ""))

becomes

LEN({"BBA";"CAC";"BBBB";"DADA";"EDA";"D";"BABB";"TTDT";"VADTE";"ADTE";"ADAD";"DEEB";"BADT";"CCDDBB";"BBCB"})

and returns

{3;3;4;4;3;1;4;4;5;4;4;4;4;6;4}

Step 4 - Subtract arrays

LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, ""))

becomes

{3;3;8;4;3;3;4;6;5;4;6;4;4;6;4}-{3;3;4;4;3;1;4;4;5;4;4;4;4;6;4}

and returns

{0;0;4;0;0;2;0;2;0;0;2;0;0;0;0}

Step 5 - Divide with cell length

(LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, "")))/LEN($E$2

becomes

{0;0;4;0;0;2;0;2;0;0;2;0;0;0;0}/LEN($E$2)

becomes

{0;0;4;0;0;2;0;2;0;0;2;0;0;0;0}/{3;3;8;4;3;3;4;6;5;4;6;4;4;6;4}

and returns

{0;0;0.5;0;0;0.666666666666667;0;0.333333333333333;0;0;0.333333333333333;0;0;0;0}

Step 6 - Sum number sin array

The SUMPRODUCT is better in this case because you are not required to enter the formula as an array formula to do the calculations.

SUMPRODUCT((LEN(B2:B16)-LEN(SUBSTITUTE($B$2:$B$16, $E$2, "")))/LEN($E$2))

becomes

SUMPRODUCT({0;0;0.5;0;0;0.666666666666667;0;0.333333333333333;0;0;0.333333333333333;0;0;0;0})

and returns 5 in cell E2.

Case insensitive formula in cell E3:

=SUMPRODUCT((LEN(B2:B16)-LEN(SUBSTITUTE(UPPER($B$2:$B$16), UPPER($E$2), "")))/LEN($E$2))

Array formula in cell E6:

=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))), "")

Get *.xlsx file

string exist in multiple cells.xlsx