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.

Get 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

This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range.  The […]

Count multiple text strings in a cell range

---

Weekly Blog EMAIL

[newsletter_signup_form id=1]

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles

---

Count rows with data

The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]

List values with past date

Question: I have a column "B" with a last name.. I have another columb with a date in it "C"... […]

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

Highlight more than once taken course in any given day

Question: My scenario is tracking employees who complete online training. I capture their name, id, class taken, and date. Any […]

How to use the BITLSHIFT function

The BITLSHIFT function calculates a number whose binary representation is shifted left by a specified number of bits. Formula in […]

Copy selected rows (checkboxes) (2/2)

This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on […]