How to Count Numbers that begin with Specific Value  in Excel

This post will guide you how to count numbers that begin with a specific value or number in Excel 2013/2016 or Excel office 365. How do I count the number of values that begin with a given specific value by using the SUMPRODUCT function and LEFT function in Excel. You can also use the COUNTIF function to count cells that begin with certain characters in Excel.

Assuming that you have a data of list in range B2:B6, and you want to get how many cells in your list which begins with a given number. For example, you wish to calculate the count of number in list where the values begin with “2020”.

Method1: COUNT Numbers that Begin with Specific Value


To count numbers in your range of list that begin with specific numbers, and you can use a formula based on the SUMPRODUCT function and LEFT function.

=SUMPRODUCT(–(LEFT(B2:B6,4)=”2020″))

count numbers that begin with1

Let’s See That How This Formula Works:

=LEFT(B2:B6,4)

count numbers that begin with3

This formula will extract the first 4 characters from the given range of cells. And it returns an array result like:

{“2019″;”2020″;”2020″;”2021″;”2023”}

count numbers that begin with2

=LEFT(B2:B6,4)=”2020″

count numbers that begin with4

The above array result will compare each value to “2020” to generate a TRUE or FALSE result. And this formula also returned as an array result of TRUE and FALSE values. Like this:

{FALSE;TRUE;TRUE;FALSE;FALSE}

count numbers that begin with5

=–(LEFT(B2:B6,4)=”2020″)

count numbers that begin with6

Then you need to use a double negative operator to convert TRUE or FALSE values to 1 and 0. It also returned an array result like:

{0;1;1;0;0}

count numbers that begin with7

SUMPRODUCT function sums the numbers in the above array and returns 2.

Method2: COUNT Numbers that Begin with Specific Value Using COUNTIF Function


You can also use the COUNTIF function to count the numbers that begin with a particular value. Like below:

=COUNTIF(B2:B6,”2020*”)

This formula use the asterisk wildcard to match any number of uncertain characters in length in your given range of cells.

#1 select Cell D3

#2 enter the above formula into Cell D3

Then you need to press Enter key to apply this formula.

count numbers that begin with8

Related Functions


  • Excel SUMPRODUCT function
    The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
  • Excel COUNTIF function
    The Excel COUNTIF function will count the number of cells in a range that meet a given criteria. This function can be used to count the different kinds of cells with number, date, text values, blank, non-blanks, or containing specific characters.etc.= COUNTIF (range, criteria)…
  • Excel LEFT function
    The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…
Related Posts

Google Sheets WORKDAY Function
google sheets workday function1

This post will guide you how to use Google Sheets WORKDAY function with syntax and examples. Description The Google Sheets WORKDAY function returns a date or a serial number that represents a date that is the indicated number of working ...

How to Use COUNTIFS function with Multiple Criteria and OR Logic in Excel
countifs function with multiple criteria or logic4

This post will guide you how to Use COUNTIFS function with multiple criteria and OR logic in Excel 2013/2016 or Excel office 365. How to count with multiple criteria and OR logic in Excel. COUNTIFS with Multiple Criteria and OR ...

How to Sum Data if by Year in Excel

In statistic, to create a yearly report for showing the market trend on different years, we often sum a total for different years, so based on the year’s total we can do estimation for the coming year. This scenario frequently ...

How to Convert Fahrenheit to Celsius or Kelvin in Microsoft Excel
convert fahrenheit to celsius3

This post will guide you how to Convert Temperatures among Fahrenheit, Celsius, and kelvin using a formula in Microsoft Excel. How do I convert Fahrenheit to Celsius in Excel 2013/2016. How to convert degrees Fahrenheit to degrees Celsius in Excel. ...

How to Change the Orientation of Worksheet in Excel
change orientation of worksheet1

This post will guide you how to change the default Portrait orientation to Landscape page orientation in your worksheet in Excel. Change the Orientation of Worksheet When you have a list of data with lots of columns, you can try ...

Limit Formula Result to Maximum or Minimum Value
limit formula result2

This post will guide you how to limit formula result to a maximum or minimum value in excel. For example, if you want to limit the results of a formula to a maximum value of 200, how to this this ...

Insert or Delete Comment using Shortcut
insert delete comments1

This post will guide you how to use shortcut to insert or delete a comment that exists in the active cell in excel. How do I quickly insert or delete a comment in an active cell with shortcut keys in ...

Excel SWITCH Function

This post will guide you how to use Excel SWITCH function with syntax and examples in Microsoft excel. Description The Excel SWITCH function compares one value against a list of values, and returns the result corresponding to the first matching ...

Excel ROUNDUP Function
excel roundup examples5

This post will guide you how to use Excel ROUNDUP function with syntax and examples in Microsoft excel. Description The Excel ROUNDUP function rounds the number up to a specified number of decimal places. It will round away from 0. ...

Fill Blank Cells with specific value (0 or null)
fill empty blank cell with value2

This post will teach you how to fill all blank cells with specific value, such as: 0 or any other values in excel. How to locate all blank cells and then fill in all the blanks with any specific value. ...

Sidebar