## 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 exist multiple times in a cell.

**Answer: **

### Array formula in cell B11:

**How to create an array formula**

- Copy array formula (Ctrl + c)
- Select cell B11
- Paste array formula (Ctrl + v) to formula bar

- Press and hold Ctrl + Shift
- Press Enter
- Release all keys

You can also use this formula to count how many times a specific character exists in a column in excel.

### Explain array formula in cell B11

=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)

**Step 1 - Replace existing text strings with new text string in named range tbl (A1:A6)**

=(SUM(LEN(tbl))-SUM(LEN(**SUBSTITUTE(tbl, $B$9, "")**)))/LEN($B$9)

Substitute(text, old_text, new_text, [instance_num]) replaces existing text with new text in a text string

SUBSTITUTE(tbl, $B$9, "")

becomes

SUBSTITUTE({"AA";"BB CC AAB";"EEF DD GG BB";"HH AAII JJ";"KK LL MMA";"NNBB AA DD"}, "AA", "")

returns this array:

{"";"BB CC B";"EEF DD GG BB";"HH II JJ";"KK LL MMA";"NNBB DD"}

**Step 2 - Return the number of characters in the named range tbl (A1:A6) without text string "AA"**

=(SUM(LEN(tbl))-**SUM(LEN(SUBSTITUTE(tbl, $B$9, "")))**)/LEN($B$9)

SUM(LEN(SUBSTITUTE(tbl, $B$9, "")))

becomes

SUM(LEN({"";"BB CC B";"EEF DD GG BB";"HH II JJ";"KK LL MMA";"NNBB DD"}))

and returns 44

**Step 3 - Return the number of characters in the named range tbl (A1:A6)**

=(**SUM(LEN(tbl))**-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)

SUM(LEN(tbl))

becomes

SUM(LEN({"AA";"BB CC AAB";"EEF DD GG BB";"HH AAII JJ";"KK LL MMA";"NNBB AA DD"}))

becomes

SUM({2;9;12;10;9;10})

returns 52.

**Step 4 - Return the number of characters in cell B9**

(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/**LEN($B$9)**

LEN($B$9)

becomes

LEN("AA")

returns 2.

**Step 5 - All together**

=(SUM(LEN(tbl))-SUM(LEN(SUBSTITUTE(tbl, $B$9, ""))))/LEN($B$9)

becomes

=(52-44)/2)

becomes

=8/2

returns 4.

**Named ranges**

tbl (A1:A6)

What is named ranges?

**Download excel example file **

count-text-string-in-a-column.xls

(Excel 97-2003 Workbook *.xls)

### Functions in this article:

**Len(**text**)
**Returns the number of characters in a text string

**Substitute(**text, old_text, new_text, [instance_num])

Replaces existing text with new text in a text string

**Sum(**number1,[number2],**)
**Adds all the numbers in a range of cells

*This blog article is one out of three articles on the same subject.*

- Count number of times a string exist in multiple cells using excel formula
- Count text that occurs multiple times in excel cell
- Count occurences of a specific text string in a column in excel

*Read more related articles in the archive.*

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

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

### 11 Responses to “Count text string in a range (case sensitive)”

### Leave a Reply to Rick Rothstein (MVP - Excel)

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

Hi! can you do this to count the number of "yes"'s in column B if column A meets the requirement of being "c"

A B

a yes

a no

b yes

b no

b no

b yes

c yes

c yes

c no

d yes

d yes

Thanks!

@Arielle,

You can use the SUMPRODUCT function to do that...

=SUMPRODUCT((A1:A1000="c")*(B1:B1000="yes"))

Adjust the ranges as needed (but make sure they are both contain the same number of cells).

Hello,

I would like to do this, but with parts of a string, is it possible?

EX:

Column A | Column B | Column C

545 contas-investimento

545 contas-Bolsa

546 contas-investimento

545 contas-investimento

Like, find how many times 545 has investimento.

Thanks.

Pedro Falcão,

Formula:

=COUNTIFS(A1:A4,E2,B1:B4,"*"&E1&"*")

Thank you for commenting!

Oscar, thank you so much.

Unfortunatly the excel i have instaled for now is the 2003, only by the end of this year my company wil install the most recent, then i will be able to use the formula you gave me.

Is there any other way to do this in excel 2003?

Pedro Falcão,

try this array formula:

=SUM(COUNTIF(E2,A1:A4)*NOT(ISERROR(SEARCH(E1,B1:B4))))

Don´t forget to enter it as an array formula:

1. Press and hold CTRL + SHIFT

2. Press Enter

Works like a charm!!!

Thank you so much.

When I use this formula. it does not work

Don,

Did you create an array formula?

I have added new instructions to this post.

Hi Oscar,

Clear explination! Thanks

If you replace the value in A1 to "AAA" then the answer should be 5

But the formula gives 4, because you substitute the found substring with nothing.

I read your suggestion on https://www.get-digital-help.com/2018/04/17/count-a-given-pattern-in-a-cell-value/

That is the right answer for 1 cell.

How do I use this formula for the search of a substring on the "tbl" from your example? Wich should give the answer 5........

Marcel Maatman

It is not possible to build an array with different number of rows and columns, like this:

1 1 1

2 3

1

This is needed for the formula to work. I need to think about this, not sure I can solve it. It can be done with a user defined function.