Count a given pattern in a cell value
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 overlapping text string in a cell.xlsx
Related articles
Count cells containing text from list
The array formula in cell F3 counts cells in column B that contains at least one of the values in [โฆ]
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 [โฆ]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are [โฆ]
Count cells between specified values
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values [โฆ]
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.
English
Spanish
