How Can You Count Numbers in a Range Based on Their Nth Digit?

If you’re working with a large dataset in Excel, you may need to count how many numbers in a given range contain a specific digit at a certain position, or “nth digit”. This can be a tedious task, especially if you’re dealing with thousands or even millions of numbers.

Fortunately, Excel provides several built-in functions and formulas that can help you automate this process and save time. In this article, we’ll explore different methods for counting numbers in a range based on their nth digit using Excel.

Defining the Problem

Before we dive into the solutions, let’s define the problem more precisely. Suppose you have a range of numbers in Excel and you want to count how many of them have a certain digit in a certain position. For example, let’s say you have a list of phone numbers and you want to count how many of them have the digit “5” in the third position.

Examples

To illustrate this problem, let’s look at some examples. Suppose we have the following range of numbers in Excel:

If we want to count how many of these numbers have “5” in the third position, the answer is two: 245 and 754.

Another example is counting how many numbers in a range have “3” in the second position (tens place):

The answer is three: 133, 347, and 335.

Solution 1: Using the IF Function and MID Function

One way to count numbers based on their nth digit in Excel is to use the IF function and the MID function. The IF function checks whether a given condition is true or false, and returns a value depending on the result. The MID function returns a specific number of characters from a text string, starting at a specified position.

To count how many numbers in a range have a certain digit in a certain position, we can combine the IF function and the MID function as follows:

=SUM(IF(MID(A1:A6,3,1)="5",1,0))

This formula counts how many numbers in the range A1:A6 have “5” in the third position. The MID function extracts the third character from each number in the range, and the IF function checks whether it’s equal to “5”. If it is, it returns 1, otherwise, it returns 0. Finally, the SUM function adds up all the 1’s and 0’s to get the total count.

Solution 2: Using the SUMPRODUCT Function and INT Function

Another way to count numbers based on their nth digit in Excel is to use the SUMPRODUCT function and the INT function. The SUMPRODUCT function multiplies corresponding components in the given arrays and returns the sum of those products. The INT function rounds a number down to the nearest integer.

To count how many numbers in a range have a certain digit in a certain position, we can use the SUMPRODUCT function and the INT function as follows:

=SUMPRODUCT(--(INT(A1:A6/10^2)=5))

This formula counts how many numbers in the range A1:A6 have “5” in the third position. The INT function divides each number in the range by 10^2 (100), rounds it down to the nearest integer, and compares it to 5. The result is an array of TRUE or FALSE values, which are converted to 1’s and 0’s by using the double unary operator (–). Finally, the SUMPRODUCT function adds up all the 1’s in the array, which gives us the count of numbers that have “5” in the third position.

Thank you.