The article below explains a more scalable approach based on the SEARCH function.
Then it compares the count of matches found with the count of values inthings.
If the counts match, the formula returns TRUE.
Otherwise, the formula returns FALSE.
If thesubstringis found, SEARCH returns the location of the match as a number.
If thesubstringis not found, SEARCH returns a #VALUE error.
The ISNUMBER function is used to convert the result from SEARCH into TRUE or FALSE.
The twist in this formula is that we are not searching for a single substring.
Instead, we are searching for 3 different substrings in the named rangethings(F5:F7).
These locations are delivered to the ISNUMBER function, which converts the results to TRUE or FALSE values.
The answer is that it will work fine in Excel 2021 or later.
you might read more about this topic here:Why SUMPRODUCT?
SEARCH Function
The Excel SEARCH function returns the location of one text string inside another.
SEARCH returns the position offind_textinsidewithin_textas a number.
COUNTA does not count empty cells.
COUNT Function
The Excel COUNT function returns a count of values that are numbers.
Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers.
Empty cells and text values are ignored….
