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.
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.
Notes: The above formula sums cells no matter where the asterisk is in a cell.
To sum cells whose asterisk is at the end of the cell, you can apply a formula like this:
To sum cells whose asterisk is at the beginning of the cell, apply the formula below:
Explanation of the formula
> "*~**": 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.
The Best Office Productivity Tools
- 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.
Please leave your comments in English