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.
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.
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”.
In the example given below formula we used is: (See screenshot)
Figure 1. Example of Cell contains all of many things function