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

Data Analysis

  • Randomize/ Shuffle List in Excel
  • How To Sort One Column or Multiple Columns in Excel
  • How to calculate correlation coefficient Correlation in Excel
  • Get column name from index in Excel Table
  • Managing Conditional Formatting Rules in Excel

References

  • How to get last row in mixed data with blanks in Excel
  • Last row number in range
  • Count unique text values with criteria
  • Perform case-sensitive Lookup in Excel
  • How to use Excel COLUMN Function

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation don’t exceed total
  • Excel Data validation date in next 30 days
  • Excel Data validation only dates between
  • Excel Data validation number multiple 100

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:

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

  • OR function Examples in Excel
  • How to return blank in place of #DIV/0! error in Excel
  • SWITCH function example in Excel
  • IF with boolean logic in Excel
  • IFS function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • How to get number of days, weeks, months or years between two dates in Excel
  • Steps to create Dynamic calendar grid in Excel
  • Convert text timestamp into time in Excel
  • MONTH function: Description, Usage, Syntax, Examples and Explanation
  • How to calculate project start date based on end date in Excel

Grouping

  • If cell contains one of many things in Excel
  • Categorize text with keywords in Excel
  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel
  • How to randomly assign data to groups in Excel

General

  • Select, Insert, Rename, Move, Delete Worksheets in Excel
  • Freeze and Unfreeze Panes in Excel
  • Spell Check in Excel
  • Creating and Opening an existing file in Excel
  • How to calculate percent change in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning