Excel Formulas - Count numbers beginning with a specific number

Count numbers beginning with a specific number

Syntax of used function(s)

SUMPRODUCT(array1, [array2], [array3], ...)
LEFT(text, [num_chars])

The SUMPRODUCT function is used to multiplies the corresponding components in the given arrays, and returns the sum of those products.
The LEFT function returns the first character or characters in a text string, based on the number of characters you specify.

What to do?

To count numbers in a range that beginning with specific number, the SUMPRODUCT function inside LEFT function can be used.

Formula

=SUMPRODUCT(--(LEFT(B6:B13,3)="957"))

How the formula works

The LEFT function inside the SUMPRODUCT function retrieve fist 3 digits of each number in the range B6:B13 and test whether this number is "957" and returns an array of value TRUE and FALSE. The formula looks like :

=SUMPRODUCT(--({TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;}))

The '--'(double negative) signs coerce the TRUE and FALSE values into 1 and 0, and the formula looks like:

=SUMPRODUCT(1;0;0;0;1;0;1;0;)

And the SUMPRODUCT function finally produced a sum of the array and returns the result.

Previous: Excel Formulas - Count number of matches between two columns
Next: Count Formulas - Count paired items in a row column combination from a guide