Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Data Series in Excel
  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • How to count table columns in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • Get column name from index in Excel Table

References

  • Count unique text values with criteria
  • How to get last row in text data in Excel
  • Find closest match in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table
  • How to get relative column numbers in a range in Excel

Data Validations

  • Excel Data validation allow uppercase only
  • Excel Data validation date in specific year
  • Excel Data validation must begin with
  • Excel Data validation exists in list
  • Excel Data validation date in next 30 days

Count numbers that begin with in Excel

by

This tutorial shows how to Count numbers that begin with in Excel using the example below;

Formula

=SUMPRODUCT(--(LEFT(range,chars)="xx"))

Explanation

To count numbers in a range that begin with specific numbers, you can use a formula based on the SUMPRODUCT function and LEFT functions.

In the example shown, the formula in E6 is:

=SUMPRODUCT(--(LEFT(B5:B11,2)="25"))

How this formula works

Inside SUMPRODUCT, we use the LEFT function on the range of numbers like this:

LEFT(B5:B11,2)

This creates an array of results like this:

{"25";"25";"35";"45";"25";"45";"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:

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

Next, we use a double negative co coerce TRUE FALSE values to 1 and zero, which creates a numeric array:

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

The SUMPRODUCT function then simply sums the elements in the array and returns 4.

Post navigation

Previous Post:

How to use Excel CHOOSE Function

Next Post:

Customize Ribbon In Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • How to use Excel XOR Function
  • Not Equal To ‘<>‘ operator in Excel
  • How to use Excel TRUE Function
  • IFS function: Description, Usage, Syntax, Examples and Explanation
  • FALSE function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Display Days in month in Excel
  • Get first day of previous month in Excel
  • Get month name from date in Excel
  • How to calculate nth day of year in Excel
  • DATE function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group numbers with VLOOKUP in Excel
  • Map text to numbers in Excel
  • If cell contains one of many things in Excel
  • Group times into 3 hour buckets in Excel
  • Map inputs to arbitrary values in Excel

General

  • Check if multiple cells have same value with case sensitive in Excel
  • How to create dynamic worksheet reference in Excel
  • Delete Blank Rows at Once in Excel
  • Subtotal invoices by age in Excel
  • How to generate random number between two numbers in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning