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

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:

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

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.