To count numbers where the nth digit is a given number, use the Excel COUNTDIGITS function or a formula based on MID and IFERROR functions.
How to count numbers by nth digit?
Steps to count numbers in a range where the nth digit is equal to a given number:
- Type the =COUNTDIGITS(B3:B10,2,5)
- Press Enter
- The formula will return the number of cells where the 2nd digit is 5.
COUNTDIGITS Function
Using the COUNTDIGITS function, you can build a formula to count the number of cells where the nth digit equals a given number.
Syntax
=COUNTDIGITS(rng, position, digit)Arguments
The function uses three required arguments:
- “rng“: the range of cells that contains numbers
- “position”: the position of the digit
- “digit“: the digit that we find in the given position of the number
Example
COUNTDIGITS is a powerful user-defined function; the goal is to simplify special actions in Excel and replace advanced formulas. In the example, select the range B3:B10 and create a named range, in this case, “data”.
We want to count the number of cells where the 2nd digit is “1”. To do that, configure the COUNTDIGITS function arguments:
- range = “data” (B3:B10)
- position = 2
- digit = 1
Based on the above-mentioned setup, apply the formula:
=COUNTDIGITS(data, 2, D3)
The result is 1 since we have only one matching value in cell B7. Copy the formula down to count numbers by nth digit.
As usual, the COUNTDIGITS function is a part of the free DataFX add-in.
Workaround with the RIGHT, MID, and COUNTIF functions
You can use text functions with the COUNTIF functions to reach the same result. In the example, our data is in the range B3:B10. In the example, you want to count cells where the second digit is 5.
The formula in cell F3:
=COUNTIF(D3:D10,"=5")
Explanation: Evaluate the formula from the inside out. First, using the MID function, we extract the first two digits from the number.
= MID(data,1,2)After that, using the RIGHT function, get the second digit.
=RIGHT(MID(data,1,2),1)The result is an array that contains the following numbers:
={5, 5, 5, 2, 5, 4, 5, 3}Finally, COUNTIF will count the numbers that are equal to 5.
=COUNTIF(D3:D10,"=5") = 5Count numbers by nth digit with SUMPRODUCT
We can reach our goal using a combination of built-in Excel functions. Another useful method is to apply the SUMPRODUCT and MID functions with Boolean logic to count the numbers of cells where the nth digit is a given number.
If you like Excel challenges, and old-fashion ways, you can use a SUMPRODUCT-based formula.
Formula:
=SUMPRODUCT(--(RIGHT(MID(data,1,2),1)="5"))Explanation: The MID function converts the numeric value to text; then, the RIGHT function gets the first two digits from the number. The formula will create an array that contains boolean values, TRUE or FALSE. The double-negative (–) method converts boolean values to 0s and 1s.
The array looks like this:
={1, 1, 1, 0, 1, 0, 1, 0}Finally, SUMPRODUCT SUMS the array elements and returns with 5.
