COUNTIF to Count If Cell Contains Text from List in Excel

Each method is demonstrated using a real-world product dataset as an example.When dealing with product descriptions or tags, it’s common to check how many of those cells contain keywords from a defined list. With Excel’s COUNTIF function combined with wildcards and arrays, you can create powerful formulas to count matches across rows or columns.

In this article, you’ll learn how to use the COUNTIF function to count matching text from a keyword list. We’ll cover how to count cells that contain any match, count how many matches exist within a single cell, and alternative syntaxes using the SUMPRODUCT function.

This method counts how many cells in a range contain any keyword from your list. Using the product list in column A and keyword list in column D, this formula counts how many product names contain any keyword from the list. The result is a single number representing total matches across the entire dataset.

We’ll use a dataset where column A holds product descriptions (e.g., clothing items), and column D lists keywords we want to flag (like Cotton, Wool, Leather, etc.). The formula will go into column B, where it will count total matches if the description in column A contains any of the listed keywords.

Steps:

➤ Select any blank cell like B2.
➤ Use this formula:

➤ Press  Ctrl  +  Shift  +  Enter  to evaluate the array in older Excel versions and only  Enter  for modern versions.

The formula returns how many cells in A2:A10 contain any keyword from D2:D6.

➤ If you prefer to avoid using array formulas, you can find the total match count using the SUMPRODUCT function:

This provides the same result as SUM and COUNTIF function, but works with a single Enter even in older Excel versions.

This method shows how many keywords from your list are found in each product name. Using the product list in column A and keyword list in column D, the formula returns a number such as 0 for no match, 1 for one match, 2 for multiple matches and so on.

Steps:

➤ In a blank cell like B2, use formula:

➤ Press  Ctrl  +  Shift  +  Enter  to evaluate the array in older Excel versions and only  Enter  for modern versions.
➤ Drag down to fill the column.

This gives a number such as 0 (no match), 1 (one match), 2 (multiple matches), etc.

Frequently Asked Questions

Does COUNTIF support partial text matching?

Yes. You can use wildcards like *text* inside the COUNTIF function to check if a part of a keyword exists anywhere in a cell. This is helpful for loosely structured or inconsistent data entries.

Will this work in older Excel versions?

Yes. All formulas are compatible with Excel 2010 and newer. In older versions without dynamic arrays, make sure to press  Ctrl  +  Shift  +  Enter  when entering formulas that rely on array behavior.

Can COUNTIF evaluate against multiple values?

Not directly but by combining the COUNTIF function with an array constant like {“Cotton”,”Wool”}, Excel processes each keyword separately and returns how many times any of them appear across your range. Wrap with SUM function for totals.

How do I check if a cell contains any value from a list?

To check if a cell contains any keyword from a list, use a formula like =IF(OR(COUNTIF(cell, “*”&list&”*”)), “Match”, “No Match”). It compares the cell content against each item in the list using wildcards for partial matches.

What happens if no keyword matches?

If none of the keywords from your list are found in the cell, these formulas will either return “No Match“, a blank cell, or an error depending on how you’ve structured your logic with IF or IFERROR function.

Wrapping Up

In this tutorial, we learned how to use Excel’s COUNTIF function to count cells that contain any value from a list. With just a few formulas, you can count how many cells match, how many items match per cell, and control behavior with wildcards and references. These methods are simple yet effective in cleaning and analyzing text-heavy data in Excel. Feel free to download the practice file and share your feedback.