< Go Back

Cell contains all of many things

The cell contains all of many things

If a cell contains all of many things and we want to make sure that all items are present in the list, we can accomplish this through a formula that contains SEARCH function, ISNUMBER, SUMPRODUCT, and COUNTA functions.

Formula

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

Explanation

The cell contains all of many things function to enable us to count all matches present in the list just the once. This count is then compared with the count of products which we want to know are present in the list. When these two counts are similar then all products are listed in a cell.

=ISNUMBER(SEARCH(things,B5)

The above part of the function searches for a single substring in a cell. If substring is present in the cell than the result is TRUE and if the cell does not contain substring than the result is FALSE.

Double negative (–) operator convert TRUE result to 1 and FALSE result to 0.

SUMPRODUCT sums the array to give us total count. When this count will be equal to a number of products inserted in “things” than the result will be TRUE.

COUNTA function identifies non-blank cells in “things”.

Example

In the example given below formula we used is: (See screenshot)

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)

Whereas;

Things= E5:E7

Figure 1. Example of Cell contains all of many things function

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar