where “include” is the named range E5:E9, and “exclude” is the named range G5:G6. In the right SUMPRODUCT, SEARCH looks for all strings in the named range “exclude”. In both parts of the formula, SEARCH returns numeric positions when strings are found, and errors when not. The ISNUMBER function converts the numbers to TRUE and errors to FALSE, and the double negative converts the TRUE FALSE values to 1 and 0. The result at this point looks like this: Then: Note: this formula returns either 1 or zero, which are handled like TRUE and FALSE in formulas, conditional formatting, or data validation.
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.
