3 Powerful COUNTIF Uses: Count by Character Length, Prefix, and Substring

For many word search queries in Microsoft Excel, data analysts default to the FIND (Ctrl+F) or =FIND() function. But when the data patterns get messy - partial matches, specific lengths, or hidden characters - the standard "equals" logic fails. That's where the secret language of wildcards comes in. By pairing the COUNTIF() function with these special symbols, you can take word search in Excel to the next level.

Think of wildcards as your Excel "skeleton keys." They allow you to unlock insights from data that isn't perfectly formatted or where you only have a piece of the puzzle. Whether you are auditing thousands of inventory SKUs or cleaning up a messy customer database, these three wildcard applications will transform you from a spreadsheet user into a data detective.

The Practical Power of the Wildcard

The Precision Strike: Counting Fixed-Length Strings

Sometimes, the length of the data tells the whole story. Imagine you are auditing a list of Product Codes where valid IDs must be exactly five characters long, or 3-digit area codes in a phone list. Anything else is an error. By using the formula =COUNTIF(datarange, "?????"), you tell Excel to count every cell that contains exactly five characters - no more, no less.

USE CASE 1: Count the number of cells with a known number of characters - e.g cells containing only three-letter words - in a range

=COUNTIF(datarange, "???")

The Head-Start: Counting Specific Beginnings

In many industries, data is categorized by the first letter. Maybe every "Service" invoice starts with an "S" and every "Product" invoice starts with a "P." Or you want to count how many customers in a CRM are based in a specific region if their IDs start with a regional prefix like "UK" or "US." To find out how many services you've performed, you don't need a complex filter. Use =COUNTIF(datarange, "S*"). The asterisk acts as a "whatever" symbol - it tells Excel: "Find an 'S' and I don't care what comes after it."

USE CASE 2. Count the number of cells that start with a particular character e.g. cells starting with the letter 's'

=COUNTIF(datarange, "s*")

The Deep Dive: Finding Hidden Keywords

This is the ultimate discovery tool. What if you need to find every customer feedback entry that mentions the word "Delay"? The word could be at the start, middle, or end of the sentence. Enter =COUNTIF(datarange, "*delay*"). By sandwiching your keyword between two asterisks, you’re telling Excel to look inside the cell and flag it if that specific string exists anywhere within.

USE CASE 3. Count the number of cells that contain a particular character e.g. cells containing the letter 's'

=COUNTIF(datarange, "*s*")

Steps to Deploy Wildcard COUNTIF

1. Identify Your Target Range

Before typing, know your datarange. This is the group of cells you want Excel to scan. For example, A2:A500.

2. Choose Your Wildcard Symbol

Decide which "key" fits your lock:

• Use ? to represent a single character.

• Use * to represent any number of characters.

3. Build the Formula

Click into the cell where you want the result. Type =COUNTIF(, select your range, add a comma, and then enter your wildcard criteria inside double quotation marks.

💡 Pro Tip: Wildcards only work with text data. If you try to use them on a column of pure numbers (like currency), Excel will return a zero. To fix this, you can convert your numbers to text using the Text to Columns wizard or by adding an apostrophe before the number.

4. Close and Execute

Close your parentheses ) and hit Enter. You’ve just performed a high-level data audit in a fraction of a second.

For a full guide to using wildcards in Excel formulas, see the Excel wildcards guide with *, ? and ~ explained