How to Count Numbers Nth Digit Equals to Specific Number in Excel

This post will guide you how to count numbers where the Nth digit equal to a specific number in Excel 2013/2016 or Excel office 365. How do I count of numbers in a given range where the Nth digit is equal to a particular number using SUMPRODUCT function and the MID function in Excel.  

General Formula:

The below general formula can help you to count the numbers where the Nth digit equal to a given number in a given range of cells in Excel. Like this:


Count Numbers Where The Nth Digit Equal To

Assuming that you have a data of list in range B2:B6, and you want to count numbers where the second digit equals to number “5”, and you can use a formula based on the SUMPRODUCT function and MID function. Like this:


Let’s See That How This Formula Works:

= MID(B2:B6,2,1)

The MID function can be used to extract the number or character from the given text string. And if you want to get the second character or number from a given range B2:B6, and you can use the above formula. And the first argument is a cell range, and the second argument specifies the start position, and the third argument indicates that the number of characters you want from the specified position of the text. This formula returns an array result like this:


This array results contain all the second digit from each cells in the given range B2:B6.


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



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:


count number n digit number7

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

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 MID function
    The Excel MID function returns a substring from a text string at the position that you specify.The syntax of the MID function is as below:= MID (text, start_num, num_chars)…
Related Posts

Check Cell If Contains One of Many with Exclusions

In Microsoft Excel Spreadsheet or google sheets, when cells contain multiple strings, how can we accomplish this task if you want to check whether these cells contain more than one given string and exclude other given strings? In this article, ...

Cash Denomination Calculator

Every country has different cash denominations, so you may need to calculate the number of different denominations based on the total amount. If it is just a small amount of cash, then you can calculate the different cash denominations manually, ...

Calculate Win Loss Tie

Suppose you got a task to calculate the win, loss, and tie totals; what would you do? If you are new to Ms Excel and don't have enough experience with it, then you might do this task manually but let ...

Count Cells that are Case Sensitive

If you are a frequent user of Microsoft Excel, you may have come across situations where you needed to add or count the cells that were case-sensitive. If you have done this task manually, I would like to point out ...

Check Dates in chronological order

Assume you have a date list that has different date formats, as seen in the accompanying picture. In this instance, Excel's Sort function will fail to sort them appropriately. However, you may convert all various date formats to a particular ...