Count cells that begin with specific text

To count cells that begin with specific text in Excel, you can use the COUNTBEGIN function or the COUNTIF function.

In this article, we will show you a custom formula to show you how to use the COUNTBEGIN function to count cells that begin a given text or a specific number.

How to count cells that begin with a specific text

1. Type =COUNTBEGIN(B3:B7,”AS”) in cell I3
2. Press Enter
3. The formula will count cells that begin with the specific text

COUNTBEGIN function

The COUNTBEGIN user-defined function is built for solving custom tasks in Excel; in other words, it is an improved version of the COUNTIF function.

Syntax:

=COUNTBEGIN(range,textToCount, [optional] caseSensitive)

Arguments:

The function uses two required and one optional argument.

• “range“:
• “textToCount“:
• “caseSensitive“: the optional arguments control the type of search. In the case of case-sensitive search (default), you can not apply the 3rd argument. If you want to apply a non-case-sensitive option, set the argument to FALSE.

Example

In the first example, you want to count cells that begin with a specific character or characters. For example, to count the number of cells that begin with the “AS” string, configure the COUNTBEGIN function arguments:

• Range = “B3:B7”
• textToCount = “AS”

The search is case-sensitive; using the third argument is unnecessary.

Formula:

=COUNTBEGIN(B3:B7,"AS")

Note: If you want to apply not case-sensitive search, modify the formula by adding the optional third argument:

=COUNTBEGIN(B3:B7,"AS", FALSE)

Count cells that begin with a specific number

The COUNTBEGIN function works well even if you want to work with numeric values.

1. Type COUNTBEGIN(D3:D10, 15) in cell D3
2. Press Enter
3. The formula will count cells that begin with the specific number

Example

In the example, the data is number format, and you want to count cells that begin with 15 (criteria).

Formula:

=COUNTBEGIN(B3:B8,15)

You can download the practice file that contains the COUNTBEGIN function.

Count cells that begin with a specified text: COUNTIF

If you are unfamiliar with user-defined functions, you can work with the built-in COUNTIF function. COUNTIF has some limitations; for example, it is not case-sensitive. In this section, we will show you a possible workaround.

Suppose that we are working with the demonstrated data set above. To count cells that begin with a certain text, use the following formula:

=COUNTIF(B3:B7,"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 one character
• asterisk (*) matches more than one character
• tilde (~) is an escape character

Related Formulas

• Count if cells less than a given number
• Count cells over N characters
• Count cells that contain numbers