To count numbers in a range that begin with specific numbers, use a formula based on the SUMPRODUCT and LEFT functions.
How to count numbers that begin with a specific digit?
- Select cell F3.
- Type =SUMPRODUCT(–(LEFT(data, 1)=”digit”)). Replace data with your range (e.g., A1:A10) and digit with the desired starting digit (e.g., 3)
- Press Enter.
- The formula will return the count of numbers that begin with the specified digit.
Example
In this example, we have a dataset in range B3:B11 containing a list of numbers. The goal is to create a formula to count how many of these numbers begin with the digit specified in cell D3, which is “3” in this case. The formula in cell F3 uses the SUMPRODUCT function combined with the LEFT function to check the first digit of each number and count matches with the specified digit.
Formula:
=SUMPRODUCT(–(LEFT(B3:B11, 1)=”3″))
Explanation
The formula uses a boolean logic. Evaluate it from the inside out.
LEFT(B3:B11, 1):
The LEFT function extracts the first character from each cell in the range B3:B11. In this example, B3:B11 contains a list of numbers, and LEFT(B3:B11, 1) isolates the first digit for each number. This function returns an array of single characters representing the first digit of each number in the range, like { “1”; “4”; “4”; “3”; “3”; “3”; “1”; “1”; “3” }.
LEFT(B3:B11, 1) = “3”:
This part checks if the extracted first character equals “3”. It creates a logical array where each element is TRUE if the first digit is “3” and FALSE otherwise. For the given range, the result would be something like { FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE }.
–(LEFT(B3:B11, 1) = “3”):
The double negative (–) converts the logical values TRUE and FALSE into numbers 1 and 0, respectively. Applying — to { FALSE; FALSE; FALSE; TRUE; TRUE; TRUE; FALSE; FALSE; TRUE } results in { 0; 0; 0; 1; 1; 1; 0; 0; 1 }. This conversion allows the SUMPRODUCT function to perform arithmetic on these values.
SUMPRODUCT(–(LEFT(B3:B11, 1) = “3”))
SUMPRODUCT multiplies the values in its arguments and sums the results. However, with only one array of numbers, it simply sums them up. In this case, it adds up all the 1s in { 0; 0; 0; 1; 1; 1; 0; 0; 1 }, which counts the occurrences where the first digit is “3”. The final result is 4, indicating that there are four numbers in the range B3:B11 that start with the digit 3.