## Sum if cells contain an asterisk in Excel

As we all know, the asterisk is a wildcard in Excel. To sum cells containing an asterisk that is regarded as a literal character not a wildcard, you can apply a formula based on the SUMIF function and the tilde (~).

#### How to sum if cells contain an asterisk in Excel?

As shown in the screenshot below, to sum sales in the range D5:D12 when the corresponding cell in range C5:C12 contains an asterisk, you can follow the steps below.

**Generic Formulas**

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

**Arguments**

**Range**: A range of cells which you want to evaluate if it contains an asterisk;

**Sum_range**: A range of cells that you want to sum.

**How to use this formula?**

Select a cell to output the result, copy or enter the below formula and press the **Enter** key.

**=SUMIF(C5:C12,"*~**",D5:D12)**

**Notes**: The above formula sums cells no matter where the asterisk is in a cell.

**=SUMIF(C5:C12,"*~*",D5:D12)**

**=SUMIF(C5:C12,"~**",D5:D12)**

**Explanation of the formula**

**=SUMIF(C5:C12,"*~**",D5:D12)**

**ROW(E5:E14)-ROW(E5)+1**:

**>**"*~**": To prevent Excel from treating asterisks as wildcards, you can add a tilde (~) before an asterisk to mark this asterisk as a literal character. Here the first and the last asterisk is a wildcard which means “one or more characters”; the asterisk after the tilde represents a literal asterisk.

The whole “*~**” means to match all cells containing an asterisk, no matter where the asterisk is in a cell.

#### Related functions

**Excel SUMIF function**

The Excel SUMIF function can help to sum cells based on one criterion

#### Related formulas

** Sum if cells contain both x and y **

This step-by-step tutorial demonstrates how to sum a range of cells if the corresponding cells containing both x and y by using the SUMIFS function.

** Sum if cells contain or equal to either x or y **

To sum the cells if contain or equal to either one value or another, this tutorial provides two formulas in detail to help solve the problems.

** Sum if date is between two dates **

This tutorial demonstrates how to sum values within a certain date range in Excel with a formula based on the SUMIFS function.

** Sum if cells are equal or not equal to a certain value **

This step-by-step tutorial explains two formulas to sum a range of cells that equal or not euqal to a certain value based on the SUMIF function.

**The Best Office Productivity Tools**

#### Kutools for Excel - Helps You To Stand Out From Crowd

**Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...**

#### Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)

- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Edge and Firefox.