The formula to count cells that end with characters

Suppose you want to count all products in the cells that have the same end of the product code. Example: product A has the code is APPLEIP7-2015 product B has the code is APPLEIP7-2016, and product C has the code is SSNOTE7-2016, now you want to count cells that end with 2016. Like this formula, We have the formula to solves above problem: =COUNTIF(range,”*SS”)

  • range represents a range of cells that contain numbers
  • x accounts for the letter that end of a value

Example:

A B
Name Code
1 A SS-NOTE7-2016
2 B SS-NOTE4-2015
3 C SS-NOTE1-2014
4 D APPLE-IPHONE7-2016
5 E APPLE-IPHONE7S-2016
6 F LG-G5-2015

Formula: =COUNTIF(A1:A6,"*2016")

The result is 3

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