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

Data Analysis

  • How to Create Column Chart in Excel
  • Filter Data Based on Date in Excel
  • How to count table columns in Excel
  • Example of COUNTIFS with variable table column in Excel
  • Number and Text Filters Examples in Excel

References

  • How to use Excel ROWS Function
  • To count total rows in a range in Excel
  • Convert text string to valid reference in Excel using Indirect function
  • How to get address of first cell in range in Excel
  • Find Closest Match in Excel Using INDEX, MATCH, ABS and MIN functions

Data Validations

  • Prevent invalid data entering in specific cells
  • Excel Data validation must begin with
  • Excel Data validation unique values only
  • Excel Data validation must contain specific text
  • Excel Data validation no punctuation

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

  • Return blank if in Excel
  • OR function: Description, Usage, Syntax, Examples and Explanation
  • How to return blank in place of #DIV/0! error in Excel
  • IF, AND, OR and NOT Functions Examples in Excel
  • How to use IFS function in Excel

Date Time

  • Get first Monday before any date in Excel
  • Series of dates by day
  • Dynamic date list in Excel
  • DATEVALUE function: Description, Usage, Syntax, Examples and Explanation
  • SECOND function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Group times into unequal buckets in Excel
  • Running count group by n size in Excel
  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel
  • Group numbers with VLOOKUP in Excel

General

  • How to get amount with percentage in Excel
  • Cell References: Relative, Absolute and Mixed Referencing Examples
  • 3D SUMIF for multiple worksheets in Excel
  • Currency vs Accounting Format in Excel
  • How to Create Calendar in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning