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

*Article updated on February 26, 2018*

**Question:** How do I count how many times a word exists in a range of cells? It does not have to be an exact match but case sensitive. Column A1:A15 is the cell range.

**Answer:**

Cell D1 is the search string. In cell D2 an array formula counts the number of times the search string is found in cell range A1:A15.

**Case sensitive array formula in cell D2:**

**Case insensitive array formula in cell D2:**

**How to create an array formula**

- Select cell D2
- Copy and paste above array formula to formula bar

- Press and hold Ctrl + Shift
- Press Enter

Array formula in cell E2:

**Not case-sensitive**

Replace FIND with SEARCH in the above formula to make it not case sensitive.

### Download *.xlsx file

string-exist-in-multiple-cellsv2.xlsx

(Excel Workbook *.xlsx)

**Functions used in this article**

**IF(**logical_test;[value_if:true];[value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**ROW(**reference**)** returns the rownumber of a reference

**COUNT(**value1;[value2]**)
**Counts the number of cells in a range that contain numbers

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**ISNUMBER(**value)

Checks whether a value is a number and returns TRUE or FALSE

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

Search for a text string in a data set and return matching records

Question: How do I find rows that contain a specific string value in a data set? Answer: Array formula in […]### 19 Responses to “Count how many times a string exists in a cell range (case insensitive)”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

Awesome. It is simply superb

How about:

=SUM(N(ISNUMBER(FIND(D1,A1:A15))))

David Hager,

Yes, your formula works!

Thanks for commenting.

Hello. I used this formula and is very useful. But how will look formula to search the exact string (an not only string who include) in column A? Also if i have a value in column B named price, i want to return the value from columb B associated to row of string searched in column A. How to make tthis? Thank you.

Adriano,

Read this post: https://www.get-digital-help.com/2009/12/29/vlookup-with-2-or-more-lookup-criteria-and-return-multiple-matches-in-excel/

Hi,

this formula is working only the data where in columns (A1:A15), i required the formula of the values in rows(A1:AZ1)

please help....

Cheran,

Try

Remember, it is an array formula.

Excellent!! But may I know is there is way I can highlight the cells

Prashant

Yes, try this CF formula:

=FIND($D$1,A1)

I have a HUGE list at the moment, and the formula stops working when changing the $A$1:$A$15 to $A$1:$A$8348. Here's what my formula looks like:

=(SUM(LEN(A1:A8348))-SUM(LEN(SUBSTITUTE($A$1:$A$8348,$D$1,""))))/LEN($D$1)

What am I doing wrong?

Haval,

did you create an array formula?

You know if you examine the formula in the formula bar. The formula is surrounded by curly brackets: {=(SUM(LEN(A1:A8348))-SUM(LEN(SUBSTITUTE($A$1:$A$8348,$D$1,""))))/LEN($D$1)}

It is good but now is case sensitive. How to do other away ?

Bob,

I have added a case insensitive formula to this post.

Thanks for commenting!

[...] Count2 Formulas to count the occurrences of text, characters, or words in Excel for Mac Count3 Count number of times a string exist in multiple cells using excel formula | Get Digital Help - Micr... I hope this resolves the problem for you, if not then I am sorry but I cannot help you further [...]

Dear Oscar,

Can it be possible ,Text of one cell filled up to others with the refference of number value entered in a other cell.i.e

A B

1 APPLE 5

Then

A B

1 APPLE 5

2 APPLE

3 APPLE

4 APPLE

5 APPLE

Amit,

I am not sure I understand.

=IF(COUNTIF($A$1:A1, $A$1:A1)<$B$1, A1, "")

How could you do this so it only counts the EXACT searchstring (so it would find AA but not AAB). I need to do this or something similar to count how many times certain numbers appear. The same number could appear in the same cell more than once. However, if i am searching for the number "1" i do not want it to also count "10" or "11".

This works! Thank you very much!!!

Dear Oscar,

I have a string in a cell as follows:

MKS3PIN-5 DC24 with PF113A-E & PFC-A1. These are model numbers of a products.

I have a list containing these and much more. "With" is not a product.

MKS3PIN-5 DC24 is a single product even though there is a space before DC24.

I want to extract the model numbers only in adjacent columns and receive a message stating that all models have been extracted. Also to say which model number is not found in the string.

The main purpose of this exercise is to arrive at the combined prices of the above combination.

Thanks & Regards

S.Narasimhan