Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get last column number in range in Excel
  • How to use Excel MATCH Function
  • How to use Excel TRANSPOSE Function
  • Count unique text values with criteria
  • Last row number in range

Data Analysis Examples

  • How to calculate current stock or inventory in Excel
  • How to combine 2 or more chart types in a single chart in Excel
  • Working With Tables in Excel
  • Excel Line Chart
  • Conditional Formatting Data bars Examples in Excel

Data Validation Examples

  • Excel Data validation date in specific year
  • Excel Data validation whole percentage only
  • Data validation must not exist in list
  • Prevent invalid data entering in specific cells
  • Excel Data validation must not contain

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.

Worked Example:   Sum lookup values using SUMIF in Excel

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:

Count numbers third digit equals 3 in Excel

Next Post:

Count numbers by range with COUNTIFS 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
  • Complete List of Excel Text Functions References and Examples
  • How to get page from URL in Excel
  • How to extract substring in Excel
  • How to convert numbers to text using TEXT function in Excel
  • Find function vs Search function in Excel
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate project start date based on end date in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Get work hours between dates custom schedule in Excel
  • Add years to date in Excel
  • PRICEMAT function: Description, Usage, Syntax, Examples and Explanation
  • COUPDAYBS function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate compound interest in Excel
  • NOMINAL function: Description, Usage, Syntax, Examples and Explanation
  • Tax rate calculation with fixed base in Excel
© 2022 xlsoffice . All Right Reserved. | Teal Smiles