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
- Type =COUNTBEGIN(B3:B7,”AS”) in cell I3
- Press Enter
- The formula will count cells that begin with the specific text
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.
=COUNTBEGIN(range,textToCount, [optional] caseSensitive)
The function uses two required and one optional argument.
- “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.
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.
Note: If you want to apply not case-sensitive search, modify the formula by adding the optional third argument:
Count cells that begin with a specific number
The COUNTBEGIN function works well even if you want to work with numeric values.
- Type COUNTBEGIN(D3:D10, 15) in cell D3
- Press Enter
- The formula will count cells that begin with the specific number
In the example, the data is number format, and you want to count cells that begin with 15 (criteria).
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:
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