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

Lookup and Reference Examples

  • Find closest match in Excel
  • Extract all partial matches in Excel
  • Offset in Excel
  • How to use Excel VLOOKUP Function
  • How to use Excel LOOKUP Function

Data Analysis Examples

  • How to Sort by Color in Excel
  • How to create Checklist in Excel
  • How to sum a total in multiple Excel tables
  • Create Scatter Chart in Excel
  • Get column index in Excel Table

Data Validation Examples

  • Excel Data validation require unique number
  • Excel Data validation number multiple 100
  • Data validation must not exist in list
  • Excel Data validation no punctuation
  • Excel Data validation with conditional list

Sum if cells contain an asterisk in Excel

by

This tutorial shows how to Sum if cells contain an asterisk in Excel using the example below;

Formula

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

Explanation

To sum if cells contain an asterisk, you can use the SUMIF function with the special “escape” character “~”. In the example shown, cell G6 contains this formula:

=SUMIF(C5:C11,"*~**",D5:D11)

This formula sums the amounts in column D when a value in column C contains “*”.

How the formula works

The SUMIF function supports wildcards. An asterisk (*) means “one or more characters”, while a question mark (?) means “any one character”.

These wildcards allow you to create criteria such as “begins with”, “ends with”, “contains 3 characters” and so on.

Because asterisks and question marks are themselves wildcards, if you want to search for these characters specifically, you’ll need to escape them with a tilde (~). The tilde causes Excel to treat the following character literally.

In this case we are using “~*” to match a literal asterisk, but this is surrounded by asterisks on either side, in order to match an asterisk anywhere in the cell. If you just want to match an asterisk at the end of a cell, use: “*~*” for the criteria.

Alternative with SUMIFS

You can also use the SUMIFS function. SUMIFS can handle multiple criteria, and the order of the arguments is different from SUMIF. With SUMIFS, the sum range always comes first in the argument list, followed by range/criteria pairs:

=SUMIFS(D5:D11,C5:C11,"*~**")

Post navigation

Previous Post:

Sum if cells are not equal to in Excel

Next Post:

Sum if by year 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
  • CLEAN function: Description, Usage, Syntax, Examples and Explanation
  • Normalize text by removing punctuations, extra spaces and more in Excel
  • PROPER function: Description, Usage, Syntax, Examples and Explanation
  • How to check if cell contains one of many things in Excel
  • How to find and replace multiple values at same time in Excel
  • Roll back weekday to Friday base on a particular date in Excel
  • Get days before a date in Excel
  • Convert Excel time to decimal hours in Excel
  • Dynamic date list in Excel
  • Get days between dates ignoring years in Excel
  • How to calculate compound interest in Excel
  • COUPNUM function: Description, Usage, Syntax, Examples and Explanation
  • Calculate payment periods for loan in Excel
  • Calculate cumulative loan interest in Excel
  • NPER function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles