where "rng" is the named range H4:H10 and contains the values to look for.
How the formula works
Each item in rng is compared to each item in values and the result is an array of TRUE or FALSE values.
The double negative will force the TRUE and FALSE values to 1 and 0 respectively. Since SUMPRODUCT receives just one array, it simply adds up the items in the array and returns the result.
Logically, any result greater than zero means that at least one value exists in the range. So, the final step is to evaluate the SUMPRODUCT result to see if its greater than zero. Any result greater than zero returns TRUE, and any result equal to zero returns FALSE.
With hard-coded values
You can also hard code the search values into the formula, using what is known as an "array constant". For example, if you want to look for 3 values: red, cyan, and magenta inside the range H2:H8, you can use:
To determine if a value exists in a range of cells, you can use a simple formula based on the COUNTIF function. In the example shown, the formula in D5 is: = COUNTIF ( rng , B5 ) > 0 where "rng" is the named range F4:F10. How this formula works...
To determine if a range or column contains specific text (a specific substring or partial text), you can use a formula based on the COUNTIF function and wildcards . In the example shown, the formula in D5 is: = COUNTIF ( rng , "*" & D5...
To test a range and determine if it contains one of many substrings (partial matches, specific text, etc.) you can use use a formula based on the COUNTIF function nested in the SUMPRODUCT function . How the formula works All the hard work is done by...
The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products. This sounds boring, but SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more...
The Excel ISNUMBER function returns TRUE when a cell contains a number, and FALSE if not. You can use ISNUMBER to check that a cell contains a numeric value, or that the result of another function is a number.
The Excel SEARCH function returns the location of one text string inside another. SEARCH returns the position of the first character of find_text inside within_text. Unlike FIND, SEARCH allows wildcards, and is not case-sensitive.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.