Count numbers by nth digit

sumproduct-double-negative

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:

  1. Open Excel
  2. Type =SUMPRODUCT(–(RIGHT(MID(data,1,2),1)=”5″))
  3. Press Enter
  4. 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″))

sumproduct-double-negative

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″)

Count-numbers-by-nth-digit-using-RIGHT-MID-COUNTIF

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:

  1. Type the =COUNTDIGITS(B3:B10,2,5)
  2. Press Enter
  3. The formula will return the number of cells where the 2nd digit is 5.
Count-numbers-by-nth-digit-in-Excel

Syntax:

=COUNTDIGITS(rng, position, digit)

Arguments: The function uses three required arguments.

  1. “rng“: the range of cells that contains numbers
  2. “position”: the position of the digit
  3. “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)

Count-numbers-by-nth-digit-in-Excel

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.