Count the Number of Cells that Start or End with Specific Text in Excel

Add to Favorites

How to count cells that match text at the start or the end of a string in Excel.

If you want a fuller explanation of counting cells that match a string, you can view our other tutorial on counting the number of cells that contain specific text in excel.

Sections:

Steps to Count Cells that Contain Specific Text at the Start or the End of the Cell

Use a Cell Reference for the Criteria

Notes

Steps to Count Cells that Contain Specific Text at the Start or the End of the Cell

To do this we will be using the COUNTIF function and an asterisk *.

  1. Type =COUNTIF(  into the cell where you want to display the count:
  2. Select the range of cells where you will search for the text.
  3. Type a comma to go to the next argument. This is where we enter the text string for which we want to search.
    To search for a string that matches only the start of what is in a cell, you use "string*" Note the asterisk that comes at the end of the string.
    To search for a string that matches only the end of what is in a cell, you use "*string" Note the asterisk at the beginning of the string.
    Here, we will match rdf only at the start of a string.
  4. Hit Enter now and you will see the result.

    There are 4 cells that start with rdf but there are 6 total cells that contain rdf.

We can fill out the COUNT for the number 4 as well, matching only when it is as the end of the cell and we get this:

Use a Cell Reference for the Criteria

If you want to use a cell reference for the criteria within the COUNTIF function instead of typing it in and hard-coding it, like above, you have to use the ampersand character.

To do this, for the formula in cell D1, we do this:

=COUNTIF(A1:A9,C1 & "*")

To do this, for the formula in cell D2, we do this:

=COUNTIF(A1:A9,"*" & C2)

This may seem confusing but this is the only way to be able to include the asterisk character in the criteria, which is what allows us to match only the start or end of a cell's contents.

Notes

This is a powerful way to count cells in Excel and it will help you to create better and more useful spreadsheets.

All matches made when using the COUNTIF function are case insensitive, which means that it will match upper case, lower case, and mixed case text all the same.

Download the spreadsheet that accompanies this tutorial so you can work with the examples we provided and test everything out.


Excel Function: COUNTIF()
Downloadable Files: Excel File

Similar Content on TeachExcel
Wildcards in Excel
Tutorial: Wildcards are characters that allow you to make more robust functions, searches, and filt...
Count the Number of Cells that Contain Specific Text in Excel
Tutorial: How to count the number of cells that contain specific text within a spreadsheet in Excel....
Count The Number of Words in a Cell or Range of Cells in Excel - UDF
Macro: Count words in cells with this user defined function (UDF). This UDF allows you to count t...
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
Macro: UDF to count the number of words in a cell or range with a user-specified delimiter. ...
Count Cells Containing TRUE or FALSE in Excel
Tutorial: Count the number of cells that contain TRUE, FALSE, or one of the two. This lets you count...
Count the Number of Unique Values in a List in Excel
Tutorial: Use a formula to count the number of unique values that are contained within a list in Exc...