Prem Rawat

Peace is Possible

— Prem Rawat

True peace begins within you. Discover the peace that already exists inside yourself, and let it bring more love, happiness and understanding into your life.

Visit YouTube Channel

How to Count Cells Containing Specific Text in Excel (Easy Guide)

Learn how to count cells containing specific text in Excel using COUNTIF, wildcards, SEARCH, and SUMPRODUCT with simple formulas and practical example
How to Count Cells Containing Specific Text in Excel (Easy Guide)

   Learn how to count cells containing specific text in Excel using the COUNTIF function, wildcard characters, and other useful formulas. Includes practical examples, case-sensitive searches, and step-by-step instructions.

How to Count Cells Containing Specific Text in Excel

When working with large Excel spreadsheets, you may need to find out how many cells contain a particular word, phrase, product name, customer name, or status. Counting these cells manually can be time-consuming, especially when your worksheet contains hundreds or thousands of records.

Fortunately, Excel provides a simple solution using the COUNTIF function. With this function, you can count cells containing specific text, whether the text appears alone or as part of a longer sentence.

In this guide, you will learn how to count cells containing specific text in Excel using practical examples. We will also explore partial text matching, multiple keywords, case-sensitive counting, and common mistakes to avoid.

Understanding How to Count Cells Containing Specific Text in Excel

The easiest way to count cells containing particular text is to use the COUNTIF function with wildcard characters.

The basic formula is:

=COUNTIF(range,"*text*")

Here is what each part means:

  • range: The group of cells you want Excel to check.

  • text: The word or phrase you want to find.

  • Asterisk (*): A wildcard that represents any number of characters.

For example, suppose cells A2:A10 contain product names, and you want to count how many cells contain the word "Laptop".

Use this formula:

=COUNTIF(A2:A10,"*Laptop*")

Excel counts every cell containing "Laptop", including entries such as "HP Laptop", "Dell Laptop", and "Laptop Stand".

You do not need to match the entire cell contents.

Using COUNTIF to Count Cells Containing Specific Text

Let's look at a practical example.

Imagine you manage an online store and maintain a spreadsheet containing customer orders.

CellProduct Name
A2HP Laptop
A3Samsung Mobile
A4Dell Laptop
A5Apple iPhone
A6Lenovo Laptop
A7Wireless Mouse
A8Laptop Bag
A9Samsung Tablet

To count how many cells contain the word "Laptop", enter:

=COUNTIF(A2:A9,"*Laptop*")

Result: 4

Excel identifies four matching entries:

  • HP Laptop

  • Dell Laptop

  • Lenovo Laptop

  • Laptop Bag

This method is particularly useful when product names contain additional words or descriptions.

Counting Cells That Contain Text From Another Cell

You do not always need to type the search word directly into your formula. You can also refer to another cell.

For example, suppose:

  • A2:A100 contains product names.

  • D2 contains the keyword you want to search for.

Use this formula:

=COUNTIF(A2:A100,"*"&D2&"*")

If D2 contains "Laptop", Excel counts all cells in A2:A100 that contain that word.

Why is this useful?

You can change the keyword in D2 without editing the formula. This makes your worksheet more flexible and is especially helpful when creating search tools or interactive reports.

Counting Cells That Contain Multiple Words

Sometimes you may want to count cells containing one of several keywords.

For example, suppose you want to count all product names containing either "Laptop" or "Mobile".

You can add two COUNTIF functions:

=COUNTIF(A2:A100,"*Laptop*")+COUNTIF(A2:A100,"*Mobile*")

This formula counts cells containing "Laptop" and adds the number of cells containing "Mobile".

Important: If a single cell contains both words, it will be counted twice.

If you want to count each matching cell only once, even when it contains both keywords, use:

=SUMPRODUCT(--((ISNUMBER(SEARCH("Laptop",A2:A100))+ISNUMBER(SEARCH("Mobile",A2:A100)))>0))

This formula counts each cell only once if it contains at least one of the specified words.

Counting Cells Containing Specific Text With Case Sensitivity

By default, COUNTIF is not case-sensitive. This means Excel treats "apple", "Apple", and "APPLE" as the same text.

If you need to count cells based on exact letter capitalization, use SUMPRODUCT with EXACT.

For example:

=SUMPRODUCT(--ISNUMBER(SEARCH("apple",A2:A100)))

The formula above is still case-insensitive.

For case-sensitive counting of cells containing a specific word, use:

=SUMPRODUCT(--ISNUMBER(FIND("apple",A2:A100)))

The FIND function distinguishes between uppercase and lowercase letters.

For example, if your data contains:

Product Name
apple mobile
Apple Laptop
APPLE Tablet
apple watch

The formula counts only entries containing the lowercase text "apple".

Result: 2

Counting Cells That Contain Specific Text in a Particular Column

You can use the same COUNTIF technique to analyse customer records, employee lists, sales reports, and other business data.

For example, imagine column B contains order statuses:

Order IDStatus
101Order Completed
102Pending Payment
103Order Completed
104Cancelled
105Order Processing
106Order Completed
107Pending Payment

To count cells containing "Completed", use:

=COUNTIF(B2:B8,"*Completed*")

Result: 3

You can also count other statuses by changing the keyword:

=COUNTIF(B2:B8,"*Pending*")

Result: 2

This approach is useful for monitoring order progress, tracking employee activities, and preparing business reports.

Counting Cells That Contain Specific Text Using SUMPRODUCT

COUNTIF is usually the simplest option, but SUMPRODUCT can be useful when you need more flexible conditions.

For example:

=SUMPRODUCT(--ISNUMBER(SEARCH("Laptop",A2:A100)))

This formula works similarly to COUNTIF with wildcards.

Here is how it works:

  • SEARCH looks for the word "Laptop" inside each cell.

  • ISNUMBER checks whether SEARCH finds a match.

  • The double negative (--) converts TRUE and FALSE into 1 and 0.

  • SUMPRODUCT adds the results to return the total number of matching cells.

This method is useful when you want to combine text matching with additional logical conditions.

Difference Between COUNTIF, SEARCH, and FIND

Excel offers several functions for working with text, but they serve different purposes.

FunctionMain PurposeCase-Sensitive?
COUNTIFCounts cells matching a conditionNo
SEARCHFinds text within another text stringNo
FINDFinds text within another text stringYes
SUMPRODUCTPerforms calculations across arraysDepends on the functions used

For most everyday tasks, COUNTIF with wildcards is the easiest choice. Use SEARCH or FIND when you need more control over how Excel identifies matching text.

Common Mistakes to Avoid When Counting Text in Excel

Even a simple COUNTIF formula can produce unexpected results if the criteria are entered incorrectly.

Here are a few things to check:

Forgetting wildcard characters

If you want to find a word anywhere inside a cell, use asterisks before and after the keyword.

Correct:

=COUNTIF(A2:A100,"*Laptop*")

Without wildcards, COUNTIF looks for a cell whose entire contents match the specified text.

Using incorrect quotation marks

Text criteria must be enclosed in double quotation marks.

Correct:

=COUNTIF(A2:A100,"*Apple*")

Using the wrong cell reference

Make sure your formula refers to the correct data range. If your records are in column B rather than column A, update the formula accordingly.

Counting the same cell more than once

When adding multiple COUNTIF formulas, remember that a cell containing two searched keywords may be counted twice.

Ignoring extra spaces

Unwanted spaces can affect exact text matching. If your data comes from external sources, consider cleaning it with the TRIM function.

Best Way to Count Cells Containing Specific Text in Excel

The COUNTIF function with wildcard characters is generally the best method for counting cells that contain a particular word or phrase.

Use the following formula as your starting point:

=COUNTIF(A2:A100,"*Apple*")

If you want a more flexible formula, connect the search keyword to another cell:

=COUNTIF(A2:A100,"*"&D2&"*")

For case-sensitive searches, use FIND with SUMPRODUCT instead.

Choosing the right formula depends on your data and what you want to count. Once you understand these methods, you can quickly analyse large datasets without manually checking individual cells.

Frequently Asked Questions (FAQs)

1. How do I count cells containing a specific word in Excel?

Use =COUNTIF(A2:A100,"*Apple*") to count cells containing the word Apple anywhere in the cell.

2. Can COUNTIF find partial text in Excel?

Yes. Use asterisks (*) before and after your search word to find partial text matches.

3. Is COUNTIF case-sensitive?

No. COUNTIF ignores uppercase and lowercase differences. Use FIND with SUMPRODUCT for case-sensitive counting.

4. How do I count cells containing text from another cell?

Use =COUNTIF(A2:A100,"*"&D2&"*"). Excel uses the keyword entered in D2.

5. Can I count cells containing multiple keywords?

Yes. You can add multiple COUNTIF formulas or use SUMPRODUCT with SEARCH to count cells containing any of several keywords without double-counting them.

COMMENTS

Loaded All Posts Not found any posts VIEW ALL Readmore Reply Cancel reply Delete By Home PAGES POSTS View All RECOMMENDED FOR YOU LABEL ARCHIVE SEARCH ALL POSTS Not found any post match with your request Back Home Sunday Monday Tuesday Wednesday Thursday Friday Saturday Sun Mon Tue Wed Thu Fri Sat January February March April May June July August September October November December Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec just now 1 minute ago $$1$$ minutes ago 1 hour ago $$1$$ hours ago Yesterday $$1$$ days ago $$1$$ weeks ago more than 5 weeks ago Followers Follow THIS PREMIUM CONTENT IS LOCKED STEP 1: Share to a social network STEP 2: Click the link on your social network Copy All Code Select All Code All codes were copied to your clipboard Can not copy the codes / texts, please press [CTRL]+[C] (or CMD+C with Mac) to copy Table of Content