## Count occurences of a specific text string in a column in excel

**Question:**

How do I count the number of times a text string exists in a column?

**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.*

### 9 Responses to “Count occurences of a specific text string in a column in excel”

### Leave a Reply

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

<code>your formula</code>

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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.