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

Data Analysis

  • How To Sort One Column or Multiple Columns in Excel
  • How to create Gauge Chart in Excel
  • Filter Data Based on Date in Excel
  • Create Scatter Chart in Excel
  • How to combine 2 or more chart types in a single chart in Excel

References

  • How to reference named range different sheet in Excel
  • How to use Excel ROWS Function
  • How to use Excel MMULT Function
  • Count rows that contain specific values in Excel
  • How to retrieve first match between two ranges in Excel

Data Validations

  • Excel Data validation don’t exceed total
  • Excel Data validation unique values only
  • Excel Data validation date in next 30 days
  • Excel Data validation no punctuation
  • Excel Data validation require unique number

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:

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 IFS function in Excel
  • Check multiple cells are equal in Excel
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • Complete List of Excel Logical Functions, References and Examples
  • NOT function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • Get work hours between dates custom schedule in Excel
  • How to calculate nth day of week in month in Excel
  • Calculate years between dates in Excel
  • Count dates in current month in Excel
  • How to calculate next scheduled event in Excel

Grouping

  • Group times into unequal buckets in Excel
  • Map text to numbers in Excel
  • Group numbers at uneven intervals in Excel
  • Group arbitrary text values in Excel
  • How to randomly assign data to groups in Excel

General

  • Check if multiple cells have same value in Excel
  • Advanced Number Formats in Excel
  • How to count total number of cells in a rectangular range in Excel
  • Freeze and Unfreeze Panes in Excel
  • Sum by group in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning