Combine the COUNTIF and LEFT functions to count cells begin with a certain character in Excel
When working with large datasets in Excel, you may often need to find out how many cells start with a specific character (or letter/number) in a given range. This can be handy for grouping, segmenting, or quickly analyzing data such as product codes, names, or categorical entries that follow a pattern. For example, items or employee names that always begin with a certain letter can be filtered and counted efficiently with suitable Excel functions or tools. This tutorial outlines several practical methods to count the number of cells that begin with a certain character, including traditional formulas, specialized Excel add-ins, and VBA code for more advanced scenarios. Each approach comes with its own advantages, so you can choose one that best matches your needs and data scenarios.
Use formulas to count cells begin with a certain character
An awesome feature to easily count cells begin with a certain character
VBA macro to count cells begin with a specific character
Use formulas to count cells begin with a certain character
Supposing you have a text string list as shown in the screenshot below, and you need to count how many of these cells begin with the character “K”. This is a common task in data validation, list checking, or filtering codes that adhere to naming rules.
To quickly accomplish this, you can use a few different formulas making use of Excel’s COUNTIF and LEFT functions (or a combination of array formulas). These allow you to identify only those cells that meet the "starts with" criterion.
1. Select a blank cell where you want to display the count result. Copy and paste the formula below into this cell, then press Ctrl + Shift + Enter (as this is an array formula in some Excel versions) to get your result.
=COUNT(IF(LEFT(A2:A6,1)="K",1,""))
Notes:
- 1) A2:A6 is the range containing the text strings to evaluate. 1 specifies you are comparing just the first character. Replace “K” with any character you need. You can adjust the range and the target character as needed for your actual data.
- 2) If you forget to use Ctrl + Shift + Enter in older Excel versions, the formula may not calculate as expected. In Microsoft 365 and Excel 2021, arrays often spill automatically.
- 3) The following formulas can also be helpful, depending on your version and preferences:
=COUNTIF(A2:A6,"K*")
This formula uses a wildcard, which matches any values in the range A2:A6 that begin with "K". No need for array input.
=SUMPRODUCT(--(LEFT(A2:A6,1)="K"))
This formula is useful if you want an alternative to array formulas, as SUMPRODUCT can process arrays natively.
Practical tips: If you want to count cells beginning with other characters or more than one initial character, simply adjust the number in the LEFT function (e.g., LEFT(A2:A6,2) for the first two characters) as well as the target string. Always double-check that your source data does not include extra spaces at the start, which can cause the count to be inaccurate.
An awesome feature to easily count cells begin with a certain character
The Select Specific Cells utility of Kutools for Excel offers a straightforward and flexible way to count the number of cells that begin with a specific character—no need to write or remember formulas. This visual, point-and-click approach is suitable for users who prefer to avoid complex syntax or need to perform quick checks repeatedly on different columns or ranges.
Before applying Kutools for Excel, please download and install it firstly.
1. Select the range of cells you want to check. Go to the Kutools tab, then click Select > Select Specific Cells. Please see the screenshot below.
2. In the Select Specific Cells dialog, configure your settings:
- 2.1) In Selection type, select Cell to check each cell individually.
- 2.2) From Specific type drop-down, choose Begins with.
- 2.3) Enter the beginning character (for example, "K") into the textbox to specify what you're looking for.
- 2.4) Click OK to execute the operation.
3. When the operation completes, another Select Specific Cells dialog will pop up, telling you exactly how many cells in the selected range begin with your specified character. At the same time, all those matching cells are selected in your worksheet. Click OK to close the dialog and view or manipulate the results further as needed.
This approach is highly recommended for users who need to quickly verify results or select matching cells for additional formatting or actions, especially in non-formula-heavy workflows. Remember that Kutools functions seamlessly support large datasets and allow for rapid, repeated operations without manual formula management.
Note: This method is case-sensitive. If the number of matches isn’t what you expect, make sure your criteria exactly match the case and the leading characters of the cells.
If you want to have a free trial (30-day) of this utility, please click to download it, and then go to apply the operation according above steps.
VBA macro to count cells begin with a specific character
For advanced users, or in automation scenarios where you need to count the number of cells beginning with a specific character across dynamic or filtered datasets, VBA provides a flexible alternative. Using a simple macro, you can perform this operation on any range—particularly useful if the counting task is repeated frequently, or if your data changes often and you don't want to update formulas repeatedly.
The macro below will prompt you to select your target range and enter the character to check for. It then analyzes each cell and displays the count in a convenient dialog. This solution is ideal for automating repetitive tasks, or when working with tables that don't allow formulas directly (such as filtered tables, protected sheets, or interactive reports). The macro is not case-sensitive unless you modify it to be so.
1. Press Alt + F11 to open the Microsoft Visual Basic for Applications editor. In the VBA window, click Insert > Module, and paste the following code into the module window:
Sub CountCellsStartingWithChar()
Dim WorkRng As Range
Dim Cell As Range
Dim FirstChar As String
Dim CountNum As Long
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select the range to count:", xTitleId, WorkRng.Address, Type:=8)
FirstChar = Application.InputBox("Enter the character to check:", xTitleId, "", Type:=2)
CountNum = 0
If WorkRng Is Nothing Or FirstChar = "" Then
MsgBox "No valid range or character specified.", vbExclamation, xTitleId
Exit Sub
End If
For Each Cell In WorkRng
If Not IsEmpty(Cell.Value) Then
If Left(Cell.Value, 1) = FirstChar Then
CountNum = CountNum + 1
End If
End If
Next
MsgBox "Number of cells beginning with '" & FirstChar & "': " & CountNum, vbInformation, xTitleId
End Sub2. Close the VBA editor, return to your worksheet, and press Alt + F8 to open the list of macros. Select CountCellsStartingWithChar and click Run. Follow the prompts to select your range and specify the target character.
Practical tips: This macro works best for text-based columns or lists. If you want to make the comparison case-insensitive, replace the Left(Cell.Value,1) = FirstChar line with LCase(Left(Cell.Value,1)) = LCase(FirstChar). Also, ensure that the selected range contains only the cells you want included, as merged cells or cells with formulas that return errors may affect results.
If you encounter “No valid range or character specified” message, try again and make sure you do not leave the prompts blank. This macro is useful not only for simple counting tasks, but also when integrating into larger automation processes (such as multi-step reports or VBA-driven dashboards).
Related articles
Use countif with multiple criteria in Excel
In Excel, COUNTIF function may help us to calculate the number of a certain value in a list. But sometimes, we need to use multiple criteria for counting, this will be more complex, today, I will talk about some items for counting with multiple criteria.
Countif by date/month/year and date range in Excel
Methods in this tutorial will guide you to Countif by date/month/year and date range with formulas in Excel.
Count if cells are start with or end with specific text in Excel
Supposing you have a range of data and you want to count the number of cells which start with “kte” or end with “kte” in a worksheet. Here I introduce some tricks instead of manual counting for you.
Countif a specific value across multiple worksheets
Supposing you have multiple worksheets and want to get the number of occurrence of a specific value “Excel” from theses worksheets. How could I count a specific values across multiple worksheet?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in