By wrapping substrings in the asterisks, Excel evaluates the formula like this: COUNTIF counts the values where ever they appear in the cell. Since we are giving COUNTIF multiple values to look for, we receive a count for each value in an array like this: {1;0;1;1} . Finally, SUMPRODUCT returns the sum of all items in the array. Any result greater than zero returns TRUE.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
