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

Data Analysis

  • Move chart to a separate worksheet in Excel
  • How To Perform and Interpret Regression Analysis in Excel
  • Randomize/ Shuffle List in Excel
  • Chart Axes in Excel
  • Create Scatter Chart in Excel

References

  • Extract data with helper column in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • How to use Excel MATCH Function
  • How to use Excel ROWS Function
  • Perform case-sensitive Lookup in Excel

Data Validations

  • Excel Data validation date in specific year
  • Excel Data validation unique values only
  • Excel Data validation allow weekday only
  • Excel Data validation allow uppercase only
  • Excel Data validation must contain specific text

Sum if ends with in Excel

by

This tutorial shows how to Sum if ends with in Excel using the example below;

Formula

=SUMIF(range,"*text",sum_range)

Explanation

To sum if cells end with specific text, you can use the SUMIF function.

In the example shown, cell G6 contains this formula:

=SUMIF(item,"*hat",amount)

This formula sums cells in the named range amount (D5:D11) only if cells in the named range item (C5:C11) end with “hat”.

Worked Example:   IF with boolean logic in Excel

Note that SUMIF is not case-sensitive. The criteria “*hat” matches any text that ends with “Hat” or “hat”.

How the formula works

This SUMIF formula depends on wildcards. In Excel, an asterisk (*) used as a wildcard means “one or more characters”, while a question mark (?) means “any one character”. This feature allows you to construct criteria such as “begins with”, “ends with”, “contains 3 characters” and so on.

Worked Example:   Sum if cells are equal to in Excel

To match all items that end with “hat” the place an asterisk (*) in front of the match text:

item,"*hat"

Note that you must enclose literal text and the wildcard in double quotes (“”).

Worked Example:   Sum if date is greater than in Excel

Alternative with SUMIFS

You can also use the SUMIFS function to sum if cells begin with. SUMIFS can handle multiple criteria, and the order of the arguments is different from SUMIF. The equivalent SUMIFS formula is:

=SUMIFS(amount,item,"*hat")

Notice that the sum range always comes first in the SUMIFS function.

Post navigation

Previous Post:

44 Practical Excel IF function Examples

Next Post:

Large with criteria 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 AND Function
  • Nested IF function example in Excel
  • Return blank if in Excel
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • Invoice status with nested if in Excel

Date Time

  • Get last weekday in month in Excel
  • TIME function: Description, Usage, Syntax, Examples and Explanation
  • Get work hours between dates custom schedule in Excel
  • Get days between dates ignoring years in Excel
  • Get first day of month in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Group arbitrary text values in Excel
  • If cell contains one of many things in Excel
  • Running count group by n size in Excel
  • Group times into unequal buckets in Excel

General

  • Count cells that do not contain many strings in Excel
  • Share Excel data with Word documents
  • How to calculate percent sold in Excel
  • How to generate random number weighted probability in Excel
  • How to calculate percent of goal in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning