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

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

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

Counting conditionally formatted cells (vba)

UPDATE: It is not possible to count conditionally formatted cells using vba as far as I know, I recommend you […]

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

Formula for matching a date within a date range

Table of contents Match a date when a date range is entered in a single cell Match a date when […]

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

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]

Count specific text string in a cell

Question: How do I count how many times a text string occurs in a cell in excel? Answer: Formula in […]

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 specific text string in a cell

Question: How do I count how many times a text string occurs in a cell in excel? Answer: Formula in […]

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

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

This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]

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

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

### 19 Responses to “Count how many times a string exists in a cell range (case insensitive)”

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

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

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