Learn how to count cells that begin with specific text in Excel using a COUNTIF formula with wildcard character matches.
How to Count cells that begin with a specified text?
- Open Excel
- Type =COUNTIF(B3:B7,”AS*”)
- The formula counts all cells that begin with “AS”.
Example
COUNTIF is a versatile function for analyzing data. It allows users to count the number of cells that meet a certain condition. This tutorial will explore using wildcards with COUNTIF to count cells that begin with a specified text string.
If you are unfamiliar with user-defined functions, you can use the built-in COUNTIF function. In the image below, we demonstrate how to use the COUNTIF function in Excel to count cells that begin with a specific text.
In this case, the formula =COUNTIF(B3:B7, “AS”) counts the number of cells in the range B3:B7 that start with the text “AS.”
The asterisk (*) is a wildcard character representing any number of characters following “AS”. Look at the criteria! You need to insert an asterisk. The “*” character is a wildcard that replaces any characters after the specified text, “AS”.
Supported wildcards as criteria argument:
- Question mark (?): Matches any single character. For example, “A?” would match “AB” or “AC”.
- Asterisk (*) matches any number of characters. For example, “AS*” would match “ASD”, “AS0001”, etc.
- Tilde (~) used to escape special characters like * or ? if you want to treat them as literal characters.
The built-in COUNTIF function can effectively count occurrences based on partial text matches, even if you are working with a large dataset.
Case-Sensitivity Limitation of COUNTIF:
One limitation of COUNTIF is that it is not case-sensitive. This means that ‘ASD-00023‘ and ‘asd-00023‘ count similarly. If case sensitivity is important, combine the COUNTIFS function with the EXACT function or even a custom function to achieve the expected results.
Related Formulas
- Count if cells less than a given number
- Count cells over N characters
- Count cells that contain numbers
- Cell contains specific text
- Formula Guide