## Count a given pattern in a cell value

*Article last updated on April 17, 2018*

The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even if it overlaps another match.

Formula in cell B6:

A regular count would result in 2 matches, see picture below.

You can find the formula here: Count specific text string in a cell

A count where overlapping is allowed returns 3 matches and this is what is demonstrated in this article.

### Explaining formula in cell B6

#### Step 1 - Build an array from 1 to the number of characters in the cell value

The LEN function counts the number of characters in cell B3.

LEN(B3) returns 13.

The INDEX function returns a cell reference based on a row number.

ROW(A1:INDEX(A1:A1000, LEN(B3)))

becomes

ROW(A1:INDEX(A1:A1000, 13))

becomes

ROW(A1:A13) and returns {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}

The ROW function returns the row number of a cell. If a cell range is used the ROW function returns an array of row numbers.

#### Step 2 - Extract all possible substrings from cell value

MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))

becomes

MID(B3, {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}, LEN(D3))

becomes

MID("nynynynyyynn", {1; 2; 3; 4; 5; 6; 7; 8; 9; 10; 11; 12; 13}, 3)

and returns the following array:

#### Step 3 - Check if substring is equal to search string

MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3

becomes

{"nyn"; "yny"; "nyn"; "yny"; "nyn"; "yny"; "nyy"; "yyy"; "yyn"; "ynn"; "nn "; "n "; " "}=D3

and returns {TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}.

#### Step 4 - Convert boolean values to the corresponding number

The SUMPRODUCT function can't handle boolean values so the SIGN function converts them into numbers. TRUE = 1 and FALSE = 0.

SIGN(MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3)

becomes SIGN({TRUE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE})

and returns {1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0}.

#### Step 5 - Count values in array

SUMPRODUCT(SIGN(MID(B3, ROW(A1:INDEX(A1:A1000, LEN(B3))), LEN(D3))=D3))

becomes

SUMPRODUCT({1; 0; 1; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0})

and returns 3 in cell B6. 1 + 0 + 1 + 0 + 1 + 0 + 0 + 0 + 0 + 0 + 0 + 0 + 0 = 3.

### Download Excel *.xlsx file

Count cells equal to any value in a list

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

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

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, […]

Count how many times a string exists in a cell range (case insensitive)

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

Count specific text string in a cell

Question: How do I count how many times a text string exists in a cell value in Excel? Answer: The […]

Count text string in a range (case sensitive)

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

Count unique distinct values that meet multiple criteria

This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

Find latest date based on a condition

Table of contents Lookup a value and find max date How to enter an array formula Explaining array formula Download […]

Count how many times a string exists in a cell range (case insensitive)

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

Count specific text string in a cell

Question: How do I count how many times a text string exists in a cell value in Excel? Answer: The […]

Count text string in a range (case sensitive)

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

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.

Table of contents Initials from first and last names Initials from first, middle and last names Create middle name initials […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

The SIGN function returns the sign of a number. 1 for a positiv number, 0 (zero) for a 0 (zero) […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

Identify overlapping date ranges

The formula in cell F6 returns TRUE if the date range on the same row overlaps another date range in […]

Overview This article describes how to create a random playlist of four teams total. Column A contains four teams. Each […]

### 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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form