## Count cells containing text from list

*Article last updated on March 25, 2018*

The array formula in cell F3 counts cells in column B that contains at least one of the values in D3:D5. Each cell is only counted once.

To enter the array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

### Explaining the formula

The TRANSPOSE function changes the values in D3:D5 from being vertically arranged to being horizontally arranged.

TRANSPOSE(D3:D5)

Note the semicolon and comma characters that separate the values below.

{"B";"L";"O"} => {"B","L","O"}

The SEARCH function requires the values to be arranged in one column in the first argument and in one row in the second argument or vice versa.

That is why the TRANSPOSE function is needed, you could, of course, enter the values horizontally on the worksheet to avoid the TRANSPOSE function.

SEARCH(TRANSPOSE(D3:D5), B3:B14) returns the following array, displayed in the picture below.

Example, B is found in character position 4 in text string N, B, L, F, I. Note that the SEARCH function returns a #VALUE error if nothing is found.

The ISNUMBER function returns TRUE or FALSE determined by a value is a number or not, it happily ignores errors.

ISNUMBER(SEARCH(TRANSPOSE(D3:D5), B3:B14)) returns the following array.

The MMULT function sums the values row by row and returns an array shown in the picture below.

MMULT(ISNUMBER(SEARCH(TRANSPOSE(D3:D5), B3:B14))*1, ROW(D3:D5)^0)

To be able to do that we must use this array as the second argument: {1;1;1} It is determined by the number of cells in the list, in this case, three. They must be 1 and arranged vertically.

That is why I built this formula that builds the array automatically: ROW(D3:D5)^0

The MMULT function can't work with boolean values so I multiply them all by 1 to convert them into 0 (zeros) or 1.

The next thing is to check if the values in the array are larger than 0 (zero).

MMULT(ISNUMBER(SEARCH(TRANSPOSE(D3:D5), B3:B14))*1, ROW(D3:D5)^0)>0

Lastly, the SUM function adds the numbers and returns a total in cell F3.

### Download Excel *.xlsx file

Count cells containing text from list.xlsx

Check out this article if you want to count all text strings found in a cell range, in other words, cells might be counted twice or more.

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

Count cells equal to any value in a list

The formula in cell F9 counts the number of cells in column B (Values1) that are equal to any of the […]

The following formula in cell D3 counts cells with values stored as text. =SUMPRODUCT(ISTEXT(B3:B14)*1) In other words, cells containing nothing, errors, […]

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

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Lookup and return multiple values concatenated into one cell

This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Filter records that contain numeric values

Liam asks: Hello Oscar, What code is needed to cause cells in Columns F - I to fill with the […]

Here comes another post about the MMULT function, today I made a dynamic scoreboard. There are five women competing and there […]

5 easy ways to VLOOKUP and return multiple values

This post explains how to lookup a value and return multiple values. No array formula required.

Match two criteria and return multiple records

Question: I have a table of 3 columns (Security name, date, price) and I have to find the price of […]

How to create a list of random unique numbers

Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]

Table of contents Initials from first and last names Initials from first, middle and last names Create middle name initials […]

How to use the SEARCH function

Returns a number representing the position a substring is, if found in a textstring. FIND is case sensitive and SEARCH is not.

Sum values containing text based on a condition

Question: I want to sum cells that have a "C" and a decimal number. The cells have other numbers and […]

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

Quickly concatenate values into one cell [No VBA]

Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]

Excelxor is such a great website for inspiration, I am really impressed by this post Which numbers add up to […]

Count overlapping days in multiple date ranges

The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]

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