  Get instant live expert help with Excel or Google Sheets “My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

# Cell Contains All of Many Things

We can use a formula that deploys the SEARCH function, with the help of ISNUMBER, SUMPRODUCT and COUNTA function to test if a cell contains all of many things in a list. For you to understand how this is done, here is a post that provides a clear guide about it. Figure 1: Testing if cell contains all of many things

## Syntax of the formula

`=SUMPRODUCT (--ISNUMBER (SEARCH (things, A1))) =COUNTA (things)`

Where;

• `=ISNUMBER (SEARCH (things, A1)` – is responsible for searching a single substring in a cell. If the cell contains a substring, the result is TRUE, if it doesn’t, then it is a FALSE.
• Double negative (-) – converts TRUE into 1 and FALSE to 0.
• SUMPRODUCT- sums the array and gives us total count. If total equals number of products inserted in “things”, result will be TRUE.
• COUNTA- identifies non-blank cells in “things”.

## How the formula works

• This formula plays a fundamental role when it comes to testing whether a cell contains all of many things.
• The formula uses SEARCH function with the help of ISNUMBER, SUMPRODUCT as well as the COUNTA functions.
• The formula counts all matches present in the list just ones.
• It then compares the count of the products we want that are present in the list.
• If the two are similar, the all the products are listed in the cell and the formula will return a TRUE result.
• If the two are not similar, the formula will return a FALSE result.

## Example Figure 2: testing if cell contains many of all things

• In our example above, our text string range is B2:B5
• We want to find out if all the cells contain all the words in range D2:D4
• We use the formula below to get the results as shown;

`=SUMPRODUCT(--ISNUMBER(SEARCH(D2:D5,C3)))=COUNTA(D2:D5)`

## Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

### Did this post not answer your question? Get a solution from connecting with the expert. Another blog reader asked this question today on Excelchat:
Solution examples Use the Vlookup Function to complete the "employee" column of table 2. Use "job Id" from table 2 as your lookup_value(s) and table 1 as your reference.
Solved by C. H. in 16 mins If a cell in another sheet is populated I need a vlookup done. If the cell is not populated I need the cell to return blank.
Solved by T. D. in 60 mins I am trying to make a chart that turns a week range red if nothing is entered in the range. If something is entered then I would like it to turn green. Please Help
Solved by E. U. in 43 mins I need a check box to show/hide an answer of an if function
Solved by Z. U. in 23 mins I want to display a range of cells if they meet a specific criteria. I want to display a name IF it does not equal one of three options AND there are any numbers contained in a range of cells
Solved by G. F. in 60 mins ## Subscribe to Excelchat.co Another blog reader asked this question today on Excelchat: