Exceljet

Quick, clean, and to the point

Count numbers that begin with

Excel formula: Count numbers that begin with
Generic formula 
=SUMPRODUCT(--(LEFT(range,2)="xx"))
Summary 

To count numbers in a range that begin with specific numbers, you can use a formula based on the SUMPRODUCT function. In the example shown, the formula in E5 is:

=SUMPRODUCT(--(LEFT(data,LEN(D5))=D5))

where data is the named range B5:B15 and the numbers in column D are entered as text values.

Explanation 

In this example, the goal is to count numbers in the range B5:B15 (named data) that begin with the numbers shown in column D. You would think this would be a good problem to solve with the COUNTIF function but for reasons explained below, COUNTIF won't work. Instead, you can use the SUMPRODUCT and Boolean logic. See below for a full explanation.

COUNTIF function

The COUNTIF function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (*,?) for partial matching. You would think you could use the COUNTIF function with the asterisk wildcard (*) like this:

=COUNTIF(data,"25*") // returns 0

The problem however is using any wildcard character means criteria must be handled as a text value in double quotes ("") whereas the values in column B are TRUE numbers. As a result, COUNTIF will never find a matching number and the result will always be zero. You might also think of this trick to coerce the numbers in data to text by concatenating an empty string ("") to the range like this:

=COUNTIF(data&"","25*") // throws error

But this will throw an error, because COUNTIF is in a group of eight functions that require an actual range for range arguments. In other words, you can't use an array operation in place of a range argument.

SUMPRODUCT function

Another way to solve this problem is with the SUMPRODUCT function, the LEFT function, and Boolean logic. To count numbers in data that begin with 25, we can use a formula like this:

=SUMPRODUCT(--(LEFT(data,2)="25")) // returns 5

Working from the inside out, the LEFT function is used to check all numbers in data like this:

LEFT(data,2)="25"

Because we give LEFT 11 numbers in the range B5:B15, LEFT returns an array with 11 results:

{"25";"25";"35";"45";"25";"45";"25";"45";"25";"35";"55"}="25"

We then compare each value to "25" to force a TRUE or FALSE result. Note that LEFT automatically converts the numbers to text, so we use the text value "25" for the comparison. The result is an array of TRUE and FALSE values:

{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}

In this array, TRUE values correspond to numbers that begin with "25" and FALSE values represent numbers that don't. We want to count these results, but first we need to convert the TRUE and FALSE values to 1s and 0s. To do this, we use a double negative (--).

--{TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}

The resulting array contains only 1s and 0s and is delivered directly to the SUMPRODUCT function:

=SUMPRODUCT({1;1;0;0;1;0;1;0;1;0;0})

With only a single array to process, SUMPRODUCT sums the items in the array and returns 5 as result. In this formula, note we are hardcoding the value "25" and the length of 2. To adapt the formula for the worksheet shown, where the numbers we want to count appear in column D, we use the LEN function and a reference to cell D5 like this:

=SUMPRODUCT(--(LEFT(data,LEN(D5))=D5))

The LEN function returns the number of characters in D5. This makes the formula more generic so it will support numbers of different lengths. Also note that the numbers in column D are entered as text values, since the result from LEFT will also be text. You can avoid this requirement by adapting the formula to coerce the value in D5 to text:

=SUMPRODUCT(--(LEFT(data,LEN(D5))=D5&""))

Here we concatenate an empty string ("") to the D5, which converts any number to text. This version of the formula will work when there are numbers in column D, or text values.

Note: In Excel 365 and Excel 2021 you can use the SUM function instead of SUMPRODUCT if you prefer. This article provides more detail.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.