Microsoft Excel Functions & Formulas

Use the SUM, LEN, and SUBSTITUTE functions to

count the occurrences of a specific word in a

range

In this example, we want to count how many times a specific word

appears in a range. Use the SUM, SUBSTITUTE, and LEN func-

tions in one array formula to do this. Enter the criterion in cell C1

and let Excel display the result of counting in cell C2.

4

To count the occurrences of a specific word in a range:

1. In cells A2:A11 type any text but enter the word test at

least once.

2. In cell C1 enter the word test.

3. Select cell C2 and type the following array formula:

=SUM((LEN(A1:A10)-LEN(SUBSTITUTE(A1:A10,

C1,"")))/LEN(C1)).

4. Press <Ctrl+Shift+Enter>.

5. Select ...