## Count multiple text strings in a cell range

This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The formula takes into account if a text string exists multiple times in a single cell, see picture above. Text strings "Car" and "bike" exist 5 times in cell range B3:B6.

It is easy to add more text strings, adjust cell range E2:F2 in the formula. The following formulas are case sensitive, however I will be demonstrating case insensitive formulas later in this article.

Array formula in cell E4:

or use this slightly larger regular **formula**:

### How to enter an array formula

- Select cell E4.
- Press with left mouse button on in the formula bar.

- Copy above array formula and paste to the formula bar.
- Press and hold CTRL + SHIFT simultaneously.
- Press Enter once.
- Release all keys.

The formula bar displays curly brackets:

**{**=SUM((LEN(A2:A5)-LEN(SUBSTITUTE(A2:A5,D1:E1,"")))/LEN(D1:E1))

**}**

Don't enter the curly brackets yourself, they appear automatically.

### Explaining array formula in cell D2

Excel has a built-in tool named "Evaluate Formula" tool, it allows you to examine formulas and the calculations they make step by step.

Go to tab "Formulas" on the ribbon, press with left mouse button on "Evaluate Formula" button and a dialog box appears.

Press with left mouse button on the "Evaluate" button on the dialog box to move to the next calculation step, this way you can see all calculation steps a formula does making it easier for you to understand and troubleshoot a formula.

The most important thing to understand in this formula is how arrays can be used.

#### Step 1 - Count characters in each cell

The LEN function returns a number representing the number of characters in a cell. In this case, the LEN function works with a cell range returning an array of numbers.

Len(B3:B6)

becomes

Len({"Car train"; "airplane bike"; "boat Car"; "rocket Car bus Car"})

and returns {9; 13; 8; 18}.

#### Step 2 - Replace existing text with the new text string

The SUBSTITUTE function replaces a specific text string in a value, it is case sensitive. The SUBSTITUTE function replaces multiple strings with nothing in this formula.

SUBSTITUTE(text, old_text, new_text, [instance_num])

The old_text argument allows you to use a cell range D1:E1 which is handy in this scenario, note that it returns an array containing eight values.

The first row in the array contains values without the first text string (D1) and the second row contains values without the second text string (E1). This is not a problem as you will see when we go through the remaining steps.

SUBSTITUTE(B3:B6,D1:E1,"")

becomes

SUBSTITUTE({"Car train"; "airplane bike"; "boat Car"; "rocket Car bus Car"},{"Car", "bike"},"")

and returns {" train", "Car train"; "airplane bike", "airplane "; "boat ", "boat Car"; "rocket bus ", "rocket Car bus Car"}

Values in an array are separated by a comma or a semicolon, commas are used between columns and semicolons between rows.

Note, your Excel version may use other characters than commas and semicolons based on the regional settings on your computer.

#### Step 3 - Count characters in each cell

In this step the LEN function calculates the length of each value in the array after the SUBSTITUTE function has replaced the given text strings.

LEN(SUBSTITUTE(B3:B6, E2:F2, ""))

becomes

LEN({" train", "Car train"; "airplane bike", "airplane "; "boat ", "boat Car"; "rocket bus ", "rocket Car bus Car"})

and returns {6, 9; 13, 9; 5, 8; 12, 18}

#### Step 4 - Subtract original character length with substituted values

This step calculates the difference between the number of characters of each value in the array. But the array sizes do not match? The number of horizontal values matches which makes it possible to calculate this arithmetic operation.

LEN(B3:B6)-LEN(SUBSTITUTE(B3:B6,E2:F2,""))

becomes

{9; 13; 8; 18} - {6, 9; 13, 9; 5, 8; 12, 18}

an returns

{3, 0; 0, 4; 3, 0; 6, 0}

#### Step 5 - Divide with length of each search string

We now know where the given text strings are in the array and also how many they are in each value based on character length. If we divide the length of each value in the array with the numbers of characters in each given text string we can calculate how many times they exist.

(LEN(B3:B6)-LEN(SUBSTITUTE(B3:B6,E2:F2,"")))/LEN(E2:F2)

becomes

{3,0;0,4;3,0;6,0}/LEN(D1:E1)

becomes

{3,0;0,4;3,0;6,0}/{3,4}

and returns {1, 0; 0, 1; 1, 0; 2, 0}

#### Step 6 - Sum all values

The SUM function adds all numbers in the array.

SUM((LEN(B3:B6)-LEN(SUBSTITUTE(B3:B6,E2:F2,"")))/LEN(E2:F2)

becomes

SUM({1,0;0,1;1,0;2,0})

and returns 5 in cell E4.

### Case insensitive counting

The following formulas are not case sensitive in terms of search values, see image above.

Array formula in cell E4:

or use this slightly larger regular **formula**:

The difference between these formulas and the case sensitive formulas is the UPPER function. It converts letters to upper case letters.

Question: How do I count how many times a word exists in a range of cells? It does not have […]

Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]

Question: How do I count the number of times a text string exists in a column? The text string may […]

The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]

The image above shows the COUNTBLANK function counting empty cells in cell range B3:B14. Note that the COUNTBLANK function ignores […]

The formula in cell F9 counts the number of cells in column B (Values1) that are equal to any of the […]

The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]

The array formula in cell F3 counts cells in column B that contains at least one of the values in […]

The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form