Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions
  • How to get address of first cell in range in Excel
  • Get nth match with INDEX / MATCH in Excel
  • Count rows that contain specific values in Excel
  • To count total rows in a range in Excel

Data Analysis Examples

  • Conditional Formatting Data bars Examples in Excel
  • How to create running total in an Excel Table
  • Data Series in Excel
  • Remove Duplicates Example in Excel
  • How to count table columns in Excel

Data Validation Examples

  • Excel Data validation must begin with
  • Excel Data validation only dates between
  • Data validation must not exist in list
  • Excel Data validation must contain specific text
  • Excel Data validation allow uppercase only

How to check if cell contains all of many things in Excel

by

If you want to test a cell to see if it contains all items in a list, you can do so with a formula that uses the SEARCH function, with help from the ISNUMBER, SUMPRODUCT, and COUNTA functions.

Case study: Let’s say you have a list of text strings in the range B5:B8, and you want to find out if these cells contain all of the words in another range, E5:E7.

You could build a formula that uses nested IF statements to check for each item, but that won’t scale well if you have a lot of things to look for.  Each time you add an word to look for you’ll need to add another nested IF and adjust parentheses.

Worked Example:   Count unique values in a range with COUNTIF in Excel

Formula

=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))=COUNTA(things)

Explanation

Solution

The solution is to to create a formula counts all matches at once. Once we have that, we simply compare that count with the count of items we’re looking for. If they match, we know a cell contains all items.

In the example shown, the formula we’re using is:

=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))=COUNTA(things)

How this formula works

The key is this snippet:

ISNUMBER(SEARCH(things,B5)

This is based on another formula (explained in detail here) that simply checks a cell for a single substring. If the cell contains the substring, the formula returns TRUE. If not, the formula returns FALSE.

Worked Example:   How to calculate project complete percentage in Excel

However, if we give the same formula a list of things (in this case, we are using a named range called “things”, E5:E7) it will give us back a list of TRUE / FALSE values, one for each item in . The result is an array that looks like this:

{TRUE;TRUE;TRUE}

Where each TRUE represents a found item, and each FALSE represents an item not found.

We can force the TRUE / FALSE values to 1s and 0s with a double negative (–, also called a double unary):

--ISNUMBER(SEARCH(things,B5))

which yields an array like this:

{1;1;1}

Next, we process this array with SUMPRODUCT, which will give us a total sum. If this sum is equal to the number of items in the named range “things”, we know we’ve found all things and can return TRUE. The way we do this is to compare the two numbers directly. We get a count of non-blank cells in “things” using COUNTA:

=COUNTA(things)

With a hard-coded list

There’s no requirement that you use a range for your list of things. If you’re only looking for a small number of things, you can use a list in array format, which is called an array constant. For example, if you’re just looking for the colors red, blue, and green, you can use {“red”,”blue”,”green”} like this:

=SUMPRODUCT(--ISNUMBER(SEARCH({"yellow","green","dog"},B5)))=COUNTA(things)
Worked Example:   Count cells that do not contain many strings in Excel

Post navigation

Previous Post:

How to Capitalize first letter in a sentence in Excel

Next Post:

How to check if cell contains one of many things in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • Remove text by matching in a cell in Excel
  • Clean and reformat telephone numbers using SUBSTITUTE function in Excel
  • LEN, LENB functions: Description, Usage, Syntax, Examples and Explanation
  • CODE function: Description, Usage, Syntax, Examples and Explanation
  • How to create email address with name and domain in Excel
  • How to get Holiday Date from Year in Excel
  • How to calculate future date say 6 months ahead in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Get day name from date in Excel
  • How to calculate Day of the Year in Excel
  • Bond valuation example in Excel
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • PRICE function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYSNC function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles