How to count if cells are start with or end with specific text in Excel?

Skip to main content
  • Login
  • Cart

Exceljet

Exceljet

Quick, clean, and to the point
  • Training
  • Videos
  • Functions
  • Formulas
  • Shortcuts
  • Blog

Count cells that begin with

Excel formula: Count cells that begin with

Generic formula 
= COUNTIF (rng,"txt*")
Explanation 

To count the number of cells that begin with certain text, you can use the COUNTIF function. In the generic form of the formula (above) rng is a range of cells, txt represents the text that cells should begin with, and “*” is a wildcard matching any number of characters.

In the example, the active cell contains this formula:

= COUNTIF (B4:B11,"app*")

How this formula works

COUNTIF counts the number of cells in the range that begin with txt by matching the content of each cell against the pattern “txt*”, which is supplied as the criteria. The “*” symbol (the asterisk) is a wildcard in Excel that means “match any number of characters”. The count of cells that match this pattern is returned as a number.

Author 
Dave Bruns

Related formulas 
Excel formula: Count cells that end with

Count cells that end with
To count the number of cells that end with specific text, you can use the COUNTIF function. In the generic form of the formula (above) txt represents the text that cells should end with, and “*” is a wildcard matching any number of characters. In…

Excel formula: Count cells that contain specific text

Count cells that contain specific text
To count the number of cells that contain certain text, you can use the COUNTIF function. In the generic form of the formula (above), rng is a range of cells, txt represents the text that cells should contain, and “*” is a wildcard matching any…

Related functions 
Excel COUNTIF function

Excel COUNTIF Function

COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,…

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you’ll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You’ll also learn how to troubleshoot, trace errors, and fix problems. Instant access.  See details here .

500 Formula Examples, thoughtfully explained.

Download 200+ Excel Shortcuts

Comments welcome! Read this before posting a question.

Topics

  • Video training
  • Function Guide
  • Formula Examples
  • Formula Criteria
  • Excel Shortcuts
  • Pivot Tables
  • 101 Excel Functions
  • Excel Tables
  • INDEX and MATCH
  • Excel Charts
  • Conditional Formatting
  • Custom Number Formats
  • Data Validation
  • Nested IF examples
  • Formula challenges
  • How-to videos
  • Excel glossary
  • Excel people
  • Excel books
  • Recent comments

Key functions

  • IF function
  • VLOOKUP function
  • S UMIFS function
  • COUNTIFS function
  • INDEX function
  • MATCH function
  • SUMPRODUCT function

Hi – I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.  Read more .

Thanks! Yall do a really good job explaining things and giving examples! -Mark

Excel foundational video course

Excel Pivot Table video training course

Excel formulas and functions video training course

Excel Shortcuts Video Course

Excel Charts video training course

Video training for Excel Tables

Excel video training

Quick, clean, and to the point.

Learn more

Please click here if you are not redirected within a few seconds.

Tip: Other languages are Google-Translated. You can visit the English version of this link.

Log in

x

or



x


x


Register

x


or



  • Office Tips & Tricks
  • Tips & Tricks for Excel
    • Tips & Tricks in German Language
    • Tips & Tricks in French Language
    • Tips & Tricks in Chinese (Simplified)
    • Tips & Tricks in Chinese (Traditional)
  • Tips & Tricks for Word
  • Tips & Tricks for Outlook

Written on .

0
0
0
s2smodern

How to 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 cells start with or end with specific text with formula

Count and select cells start with or end with specific text with Select Specific Cells good idea3


Countif cells start with or end with specific text with formula

Here I tell you some formulas to count cells which start with or end with a specific text in a specified range.

Suppose you have a list of data as below screenshot shown:

doc countif start with 1

Count if cells are starting with specific text

1. Select a cell next to the list, and type this formula =IF(LEFT(A1,3)=”kte”,”YES”,”NO”), press Enter key, then drag fill handle over the cells needed to apply this formula, and YES indicates the cell next to the formula is starting with “kte”. See screenshot:

Tip: In above formula, A1 is the cell needed to apply formula, kte is the criteria, and 3 is the number of characters of kte.

doc countif start with 2

2. Then you just need to count the number of cells which contain YES. Select a blank cell and type this formula =COUNTIF($C$1:$C$10,”YES”) and press Enter key to get the counting result.

doc countif start with 3

Count if cells are ending with specific text

1. Select a cell next to the list, and type this formula =IF(RIGHT(A1,3)=”kte”,”YES”,”NO”), press Enter key, then drag fill handle over the cells needed to apply this formula, and YES indicates the cell next to the formula is start with kte. See screenshot:

Tip: In above formula, A1 is the cell needed to apply formula, kte is the criteria, and 3 is the number of characters of kte.

doc countif start with 4

2. Then you just need to count the number of cells which contain YES. Select a blank cell and type this formula =COUNTIF($C$1:$C$10,”YES”) and press Enter key to get the counting result.

Above formulas can count cells that start or end with specified text easily in one range. however, if you need to count cells which stay in different ranges, you have to repeat applying the formulas for many times. In this case, let’s move to next method.


Count and select cells start with or end with specific text with Select Specific Cells

If you want to count and highlight the cells which start or end with a specific text, you can apply Kutools for Excel’s Select Specific Cells utility.

Kutools for Excel, with more than 120 handy Excel functions, enhance your working efficiency and save your working time.
Go to Download
Free Trial 60 days Purchase
PayPal / MyCommerce

After free installing Kutools for Excel, please do as below:

1. Select the data range and click Kutools > Select > Select Specific Cells. See screenshot:

doc count if start with10

2. In the popping dialog, select Cell in the Selection type section, select Begins with or Ends with as you need from the list of Specific type, and type kte in next text box. See screenshot:

doc countif start with 6

3. Click Ok and a dialog pops out to remind you the counting result, and click OK to select these cells.

doc countif start with 7

Tip:

If you want to select and count cells which start with “k” and end with “e” simultaneously, you can do as below:

1. Select data range and enable Select Specific Cells dialog, check And option, and select Begins with in first list, and type k in next box, and select Ends with in second list, and type e in the box. See screenshot:

doc countif start with 8

2. Click Ok, and a dialog pops out to tell you the counting result and the cells are highlighted.

doc countif start with 9

Click here to know more about Select Specific Cells.

pay attention1If you are interested in this addi-in, click here to download the 60-days free trial.

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don’t need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools…

Screen shot of Kutools for Excel

btn read more       btn download     btn purchase


  • Comment

  • RSS
  • Login

  • Sort by Newest

    • Best
    • Popular
    • Newest
    • Oldest
You are guest
( Sign Up? )
or post as a guest, but your post won’t be published automatically.




Loading comment…


The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.

    sunil


    ·

    11 days ago

    Helpful article!
    • Reply

    • Share