Count cells containing text from list
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.
Recommended articles
This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]
Count category
Question: How do I count how many times a word exists in a range of cells? It does not have […]
Table of Contents Count a specific text string in a cell Count text string in a range (case sensitive) Count […]
Question: How do I count the number of times a text string exists in a column? The text string may […]
The formula in cell B17 counts rows in cell range B3:D17 when at least one cell per row contains data. […]
The COUNTIF function is very capable of counting non-empty values, I will show you how in this article. Excel can […]
The image above shows the COUNTBLANK function counting empty cells in cell range B3:B14. Note that the COUNTBLANK function ignores […]
The formula in cell F9 counts the number of cells in column B (Values1) that are equal to any of the […]
Table of Contents Count cells with text Count cells with text excluding cells containing a space character Count text values […]
The formula in cell B6 counts how many times the string (D3) is found in a cell value (B3) even […]
This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]
Count values category
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
This article explains how to count cells highlighted with Conditional Formatting (CF). The image above shows data in cell range […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article demonstrates formulas that calculate the number of cells between two values, the first scenario involves two search values […]
Formula in C12: =SUM(1/COUNTIF($B$3:$B$8, $B$3:$B$8))+SUM(IF(COUNTIF($B$3:$B$8, $D$3:$D$8)=0, 1/COUNTIF($D$3:$D$8, $D$3:$D$8), 0)) How to create an array formula Double press with left mouse […]
This article demonstrates a formula that counts unique distinct values filtered from an Excel defined Table. Debra Dalgleish described in […]
The array formula in cell D3 calculates the number of unique distinct items based on the given date in column B. […]
The array formula in cell E3 counts unique distinct items for all dates within the same week. Example, week 2 […]
This article demonstrates how to construct a formula that counts unique distinct values based on a condition. The image above […]
This article demonstrates formulas that count values in cell based on a delimiting character. The image above shows a formula […]
The MEDIAN function lets you count overlapping dates between two date ranges. If you have more than two date ranges […]
This post demonstrates a formula in cell D16 that counts overlapping dates across multiple date ranges. The date ranges are […]
davidlim asks: re: Count records between two dates and a criterion based on the example, i was looking for 1 […]
This article demonstrates techniques on how to count cells based on the background color. I will also demonstrate user defined […]
The formula in cell E16 counts the number of cells between value B and H, Value B is in cell B3 […]
In the previous post I explained how to count overlapping dates comparing a single date range against multiple date ranges. […]
This article demonstrates ways to count contiguous values in a column, in other words, values that repeat and are adjacent. […]
Question: My issue is that I get the date in this format: 7/23/2011 7:00:00 AM I am trying to count […]
NETWORKDAYS function returns the number of whole workdays between two dates, however the array formula I am going to demonstrate […]
Janib Soomro asks: In A column, there are dates in mmddyyy format and in B column, there are two variables […]
Functions category
This post explains how to lookup a value and return multiple values. No array formula required.
This article demonstrates how to find a value in a column and concatenate corresponding values on the same row. The […]
This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]
Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]
Question: How do i return multiple results for one lookup value? I have tried to use multiple vlookups, with no […]
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]
This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This post demonstrates how to build an array formula that counts unique distinct values based on criteria. What's on this […]
Question: How do I create a random list of unique numbers from say 1 to 10, without using VBA and […]
Lookup with criteria and return records.
Joining multiple cell values in Excel is not easy, for example, the CONCATENATE function allows you to only reference a […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Table of Contents Sort a column - Excel 365 Sort a column using array formula Two columns sorting by the […]
Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]
This article demonstrates formulas that extract the nearest number in a cell range to a condition. The image above shows […]
This article demonstrates array formulas that search for cell values containing a search string and returns corresponding values on the […]
Today I have two functions I would like to demonstrate, they calculate all possible combinations from a cell range. What […]
Functions in this article
More than 1300 Excel formulas
Excel formula categories
Excel categories
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
Paste image link to your comment.