Count cells that begin with specific text

Count cells that begin with a specified text: COUNTIF

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?

  1. Open Excel
  2. Type =COUNTIF(B3:B7,”AS*”)
  3. 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”.

Count cells that begin with specific text

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.