To count numbers where the nth digit is a given number in Excel, use the SUMPRODUCT function or the RIGHT, MID, and COUNTIF functions.

## How to count numbers by nth digit?

Steps to count numbers by the nth digit in Excel:

- Open Excel
- Type =SUMPRODUCT(–(RIGHT(MID(data,1,2),1)=”5″))
- Press Enter
- The formula will return the number of cells where the 2nd digit is 5.

### Example

We can reach our goal **using a formula** based on 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-fashioned 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.

## 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″) = 5

## COUNTDIGITS Function

Using the COUNTDIGITS **user-defined function**, you can build a formula to count the number of cells where the nth digit equals a given number. The function is a part of our **free add-in**.

**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.

**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

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:

Based on the setup mentioned above, 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 the nth digit.