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

Data Analysis

  • How to create a Histogram in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • How To Load Analysis ToolPak in Excel
  • Use Data Form to input, edit and delete records in Excel
  • How to Use Solver Tool in Excel

References

  • Two-way lookup with VLOOKUP in Excel
  • Last row number in range
  • How to use Excel FORMULATEXT function
  • Extract all partial matches in Excel
  • Lookup entire row in Excel

Data Validations

  • Excel Data validation with conditional list
  • Excel Data validation no punctuation
  • Excel Data validation date in specific year
  • Excel Data validation must contain specific text
  • Excel Data validation only dates between

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 Excel XOR Function
  • Invoice status with nested if in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • Excel If, Nested If, And/Or Criteria Examples
  • IF with boolean logic in Excel

Date Time

  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • How to join date and text together in Excel
  • Series of dates by day
  • Get first day of previous month in Excel
  • How to calculate next day of week in Excel

Grouping

  • Map inputs to arbitrary values in Excel
  • Map text to numbers in Excel
  • How to randomly assign people to groups in Excel
  • Calculate conditional mode with criteria in Excel
  • If cell contains one of many things in Excel

General

  • How to calculate percentage of total in Excel
  • How to calculate percent variance in Excel
  • Basic error trapping example in Excel
  • How to calculate percent change in Excel
  • How to count total number of cells in a rectangular range in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning