Sum If Cells Contain Asterisk: Excel & Sheets

This article explains how to sum cells that contain an asterisk (*) in Microsoft Excel and Google Sheets. You will learn the exact formulas for summing cells where the asterisk appears anywhere, at the start, or as a literal character, and you will see how to avoid the common pitfall where the asterisk acts as a wildcard instead of a text character.

Formula

The core formula for summing cells that contain an asterisk anywhere in the text is:

SUMIF(range, “*~**”, sum_range)

Breaking it down:

• range – the cells to check for the asterisk (e.g., A2:A10).
• “*~**” – the criteria. The first and last asterisks are wildcards meaning “any textâ€�. The middle ~* tells Excel/Sheets that the asterisk after the tilde is a literal character, not a wildcard. So the whole criteria means “contains at least one asteriskâ€�.
• sum_range – the cells to add up (e.g., B2:B10). If omitted, Excel sums the range itself.

If you want to sum cells where the asterisk is at the beginning only, use "~**" (literal asterisk followed by any text). For cells that are exactly a single asterisk, use "~*".

Worked Example

Suppose column A has: A2: "apple*", A3: "*banana", A4: "cherry", A5: "date*" and column B has corresponding values: 10, 20, 30, 40. You want to sum B for rows where A contains an asterisk.

1. Range: A2:A5
2. Criteria: “*~**” (contains an asterisk)
3. Sum_range: B2:B5
4. Rows matching: A2 (apple*), A3 (*banana), A5 (date*) – A4 does not match.
5. Sum = 10 + 20 + 40 = 70

If you used the formula =SUMIF(A2:A5,"*~**",B2:B5) in Excel or Google Sheets, the result would be 70.

Why the Asterisk is Tricky

In Excel and Google Sheets, the asterisk (*) is a wildcard that matches any sequence of characters. For example, "*cat*" matches any cell containing “catâ€�. This is useful for partial matches, but it means that if you write "**" as a criteria, it matches everything (because the first * matches any text and the second * also matches any text). To treat an asterisk as a literal character, you must prefix it with a tilde (~). So "~*" means a literal asterisk. This tilde escape works for the question mark (?) and tilde itself as well.

Common mistake: using "*" as criteria – this matches any cell that is not empty, not cells containing an asterisk. Another mistake: using "*~*" without the trailing wildcard – this matches cells that start with an asterisk, not cells that contain one anywhere.

How to Choose the Right Criteria

Depending on what you need, the criteria changes:

• Contains an asterisk anywhere: "*~**" – matches any text with at least one asterisk.
• Starts with an asterisk: "~**" – literal asterisk followed by any text.
• Ends with an asterisk: "*~*" – any text followed by a literal asterisk.
• Exactly one asterisk (no other characters): "~*" – only the literal asterisk.
• Contains two asterisks: "*~*~**" – any text, literal asterisk, any text, literal asterisk, any text.

If you need to match an asterisk that is followed by a question mark, you would need to escape both: "*~*~?*" – but note that the question mark also needs a tilde.

Using SUMIFS for Multiple Conditions

If you have multiple conditions – for example, sum cells that contain an asterisk and also have a value greater than 5 – use SUMIFS. The syntax is:

SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)

For our example, to sum B where A contains an asterisk and B > 5:

=SUMIFS(B2:B5, A2:A5, "*~**", B2:B5, ">5")

This would sum only the values that meet both conditions. Note that SUMIFS always requires the sum range first, unlike SUMIF.

Google Sheets Specifics

Google Sheets uses the same tilde escape mechanism. However, there is a subtle difference: in Google Sheets, the SUMIF function does not require the sum_range if you want to sum the range itself, but it is good practice to include it. Also, Google Sheets supports regular expressions with functions like SUMIF when you use the REGEXMATCH function in an array formula, but for simple asterisk matching, the tilde method works identically.

If you need to sum cells that contain an asterisk and are not blank, you can combine with SUMPRODUCT for more control:

=SUMPRODUCT((ISNUMBER(SEARCH("*",A2:A5)))*B2:B5)

But note that SEARCH treats * as a wildcard, so you would need SUBSTITUTE to handle it. The tilde method is simpler.

Common Problems and Solutions

• Formula returns 0: Check that the criteria is exactly "*~**" (with quotes). If you typed it without the tilde, it will match everything and sum all values, not just those with asterisks.
• Formula sums all cells: This happens when you use "*" alone. Remember that a single asterisk matches any text.
• Asterisk is at the start but you want anywhere: Use "*~**" not "~**".
• Cells contain asterisk as part of a number (e.g., 3*4): The same criteria works because Excel treats the cell as text if it contains a non-numeric character.
• Case sensitivity: SUMIF is not case-sensitive, so it does not matter if the asterisk is uppercase or lowercase (asterisks have no case).

Alternatives to SUMIF

If you need more flexibility, consider these alternatives:

• SUMPRODUCT: =SUMPRODUCT((ISNUMBER(FIND("*",A2:A5)))*B2:B5) – FIND is case-sensitive and does not treat * as a wildcard, so this works without tilde.
• FILTER (Excel 365 / Google Sheets): =SUM(FILTER(B2:B5, ISNUMBER(FIND("*",A2:A5)))) – modern and readable.
• Helper column: Add a column with =ISNUMBER(FIND("*",A2)) and then SUMIF on that column.

Each method has its place. SUMIF is the simplest for a single condition; SUMPRODUCT and FILTER are better for complex logic.

Frequently Asked Questions

How do I sum cells that contain an asterisk and are not blank?

Use the same criteria "*~**" – it already requires at least one character (the asterisk), so blank cells are excluded. If you also need to exclude cells with only spaces, add a condition or use a helper column.

Why does my formula with “*” sum everything?

Because a single asterisk is a wildcard that matches any text. To match a literal asterisk, you must escape it with a tilde: "~*" for exactly one asterisk, or "*~**" for any text containing an asterisk.

Can I use this to count cells instead of summing?

Yes, replace SUMIF with COUNTIF: =COUNTIF(range,"*~**") counts cells that contain an asterisk. The criteria works the same way.

Does this work with numbers that have an asterisk, like “3*4”?

Yes, because the cell is stored as text. The criteria matches any text containing an asterisk, regardless of other characters. The sum will add the values in the sum range, not the numeric value of the text.

What if I want to sum cells that contain an asterisk and a specific word?

Use multiple criteria with SUMIFS. For example, to sum B where A contains an asterisk and also contains “appleâ€�: =SUMIFS(B:B, A:A, "*~**", A:A, "*apple*"). Both conditions must be true.

Is there a way to sum cells that contain an asterisk without using a tilde?

Yes, use SUMPRODUCT with FIND: =SUMPRODUCT((ISNUMBER(FIND("*",A2:A5)))*B2:B5). FIND treats * as a literal character, so no tilde is needed.

Related

• Free Image Hosting Services: Top Picks & Guide
• Repair 7zip File: Fix Corrupt Archives
• RAG Evaluation Metrics: What Each Score Tells You

in t | Total time (min) | min | 120 | 0
in d | Distance (km) | km | 10 | 0
out pace | Average pace | min/km | t / d | 2
out speed | Average speed | km/h | d / (t / 60) | 2