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

Lookup and Reference Examples

  • Vlookup Examples in Excel
  • Perform case-sensitive Lookup in Excel
  • Multi-criteria lookup and transpose in Excel
  • Two-column Lookup in Excel
  • Two-way lookup with VLOOKUP in Excel

Data Analysis Examples

  • Get column name from index in Excel Table
  • How to create dynamic reference table name in Excel
  • How to Create Thermometer Chart in Excel
  • Remove Duplicates Example in Excel
  • Excel Bar Chart

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation must contain specific text
  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only
  • Excel Data validation must begin with

If cell begins with x, y, or z in Excel

by

This tutorial shows how to calculate If cell begins with x, y, or z in Excel using the example below;

Formula

=SUM(COUNTIF(A1,{"x*","y*","z*"}))>0

Explanation

To test values to see if they begin with one of several characters (i.e. begin with x, y, or z) , you can use the COUNTIF function together with the SUM function.

In the example shown, the formula in C5 is:

=SUM(COUNTIF(B5,{"x*","y*","z*"}))>0

How this formula works

The core of this formula is COUNTIF, which is configured to count three separate values using wildcards:

COUNTIF(B5,{"x*","y*","z*"}

The asterisk (*) is a wildcard for one or more characters, so it is used to create a “begins with” test.

Worked Example:   Count unique text values in a range in Excel

The values in the criteria are supplied in an “array constant”, a hard-coded list of items  with curly braces on either side.

When COUNTIF receives the criteria in an array constant, it will return multiple values, one per item in the list. Because we are only giving COUNTIF a one-cell range, it will only return two possible values for each criteria: 1 or 0.

Worked Example:   Break ties with helper column and COUNTIF in Excel

In cell C5, COUNTIF evaluates to {0,0,0}. In cell C9, COUNTIF evaluates to: {0,1,0}. In each case, the first item is the result of  criteria “x*”, the second is from criteria “y*”, and the third result is from criteria “z*”.

Because we are testing for 3 criteria with OR logic, we only care if any result is not zero. To check this, we add up all items using the SUM function, and, to force a TRUE/FALSE result, we add “>0” to evaluate the result of SUM. In cell C5, we have:

=SUM({0,0,0})>0

Which evaluates to FALSE.

Worked Example:   Excel Rank without ties Example

More criteria

The example shows 3 criteria (begins with x, y, or z) , but you add more criteria as needed.

Conditional formatting

Since this formula returns TRUE / FALSE, you can use it as-is to highlight values using conditional formatting.

Post navigation

Previous Post:

Force negative numbers to zero in Excel

Next Post:

If cell contains 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
  • How to count total characters in a range in Excel
  • Remove leading and trailing spaces from text in one or more cells in Excel
  • BAHTTEXT function: Description, Usage, Syntax, Examples and Explanation
  • DOLLAR function: Description, Usage, Syntax, Examples and Explanation
  • CONCAT function: Description, Usage, Syntax, Examples and Explanation
  • Convert date to month and year in Excel
  • How to calculate working days left in month in Excel
  • DAY function: Description, Usage, Syntax, Examples and Explanation
  • Calculate total hours that fall between two times in Excel
  • Convert text to date in Excel
  • Compound Annual Growth Rate CAGR formula examples in Excel
  • How to calculate simple interest in Excel
  • ODDLPRICE function: Description, Usage, Syntax, Examples and Explanation
  • PRICEMAT function: Description, Usage, Syntax, Examples and Explanation
  • INTRATE function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles