Count multiple text strings in a cell range
The array formula below counts how many times multiple text strings exist in a cell range. It is easy to add more text strings, adjust cell range D1:E1 horizontally. The formula takes into account if a text string exists multiple times in a single cell, see picture below.
Text strings "Car" and "bike" exist 5 times in cell range B3:B6.
Case sensitive counting
Array formula in cell E4:
or use this slightly larger regular formula:
Case insensitive counting
Array formula in cell D2:
or use this slightly larger regular formula:
How to enter an array formula
 Select cell D2

Click in formula bar
 Paste above array formula
 Press and hold CTRL + SHIFT
 Press Enter
The formula bar displays curly brackets:
Explaining array formula in cell D2
Step 1  Count characters in each cell
Len(A2:A5)
becomes
Len({"Car train"; "airplane bike"; "boat Car"; "rocket Car bus Car"})
and returns {9; 13; 8; 18}
Step 2  Replace existing text with new text string
SUBSTITUTE(A2:A5,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"}
Step 3  Count characters in each value
LEN(SUBSTITUTE(A2:A5, D1:E1, ""))
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
LEN(A2:A5)LEN(SUBSTITUTE(A2:A5,D1:E1,""))
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
(LEN(A2:A5)LEN(SUBSTITUTE(A2:A5,D1:E1,"")))/LEN(D1:E1)
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
SUM((LEN(A2:A5)LEN(SUBSTITUTE(A2:A5,D1:E1,"")))/LEN(D1:E1))
becomes
SUM({1,0;0,1;1,0;2,0})
and returns 5 in cell D2
Download excel *.xlsx file
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 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 [โฆ]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are [โฆ]
Sum values between two dates and based on a condition
In this post, I will provide a formula to sum values in column (Qty) where a column (Date) meets two [โฆ]
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 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 [โฆ]
Converts a value to upper case letters. Formula in cell C3: =UPPER(B3) Excel Function Syntax UPPER(text) Arguments text Value to convert. Required. Excel [โฆ]
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