How to Sum If Cells Contain An Asterisk in Excel

Why This Task Matters in Excel

In thousands of workbooks created every day, a single asterisk (*) can completely change the meaning of a code, the validity of a part number, or the priority of a customer record. Manufacturers use asterisks in SKU numbers to flag obsolete stock, service organizations append an asterisk to ticket IDs that require escalation, and finance teams mark one-off journal entries with an asterisk so they can be reversed later. When you need to add up only the rows that include that tiny symbol, a simple SUM function is not enough—you must tell Excel to look for the asterisk and act on it.

The challenge is twofold. First, the asterisk is not an ordinary character in Excel’s lookup engine—it is a wildcard. When it appears in a criteria argument, Excel normally interprets it as “any sequence of characters.” That means the act of looking for a literal asterisk demands special handling. Second, the location of the asterisk inside the text string is rarely consistent. Sometimes it appears at the beginning, other times in the middle or at the end, and occasionally multiple asterisks are scattered throughout the same cell. Consequently, the formula needs to be flexible enough to “see” the asterisk wherever it resides.

Being able to sum only the rows that contain an asterisk unlocks cleaner reporting and faster decision-making. Inventory managers can instantaneously value obsolete stock, financial controllers can reconcile exceptional entries, and analysts can create dynamic dashboards that isolate or exclude flagged items with a single change of criteria. Without this skill, teams resort to time-consuming manual filters, error-prone copy-pastes, or VBA scripts that few people understand. Mastering it also sharpens your broader wildcard knowledge, which applies to filtering, conditional formatting, Power Query, and even database queries. In short, knowing how to sum if cells contain an asterisk is a foundational Excel competency that prevents costly mistakes and elevates the sophistication of your data workflows.

Best Excel Approach

For most situations, the simplest, fastest, and most transparent approach is to combine the SUMIF or SUMIFS function with the tilde (~) escape character. The tilde tells Excel to treat the next character literally, overriding its default wildcard behavior. By surrounding the tilde-escaped asterisk with regular wildcard asterisks, you create a flexible pattern that finds the asterisk anywhere in the cell.

Syntax for a single-criterion sum (SUMIF):

=SUMIF(criteria_range,"*"&"~*"&"*",sum_range)

Syntax for multi-criteria sums (SUMIFS):

=SUMIFS(sum_range,criteria_range,"*"&"~*"&"*",other_range,other_criteria,…)

Why this is the best default:

1. Performance – SUMIF/SUMIFS is optimized in the Excel calculation engine and remains fast even on hundreds of thousands of rows.
2. Simplicity – One concise formula is easier to audit than nested array formulas.
3. Compatibility – Works in every modern Excel version: desktop, Mac, Excel Online, Microsoft 365, and even older perpetual licenses back to Excel 2007.
4. Dynamic – Changes automatically when you edit underlying data; no helper columns required.
5. Readability – Business users recognize the SUMIF family quickly in shared workbooks.

Reserve alternative methods—such as SUMPRODUCT with SEARCH/FIND, FILTER with SUM, or array formulas—for edge cases where you must ignore case sensitivity, include multiple patterns, or perform extra calculations at the same time.

Parameters and Inputs

To deploy SUMIF or SUMIFS successfully, you need to understand each argument and prepare your data accordingly:

• criteria_range
  – A single-column or single-row range that contains the text strings you want to test.
  – Data type: text (although numbers formatted as text also qualify).
  – Size must match sum_range exactly (number of rows and columns).

• \"\"&\"~\"&\"*\"
  – A concatenated text string that says: any characters before the literal asterisk, the literal asterisk itself, and any characters after it.
  – The leading and trailing asterisks act as wildcards; the tilde (~) escapes the middle asterisk so it is treated literally.

• sum_range
  – The numeric range containing values to add.
  – Must be the same size and shape as criteria_range; otherwise SUMIF returns incorrect results or a #VALUE! error.

• Optional additional criteria pairs (SUMIFS only)
  – Provide more range, criteria pairs to narrow the sum.
  – Each additional range must be the same shape as sum_range.

Data preparation tips:

– Remove excess spaces with TRIM or CLEAN before applying formulas; hidden spaces can cause unexpected mismatches.
– Confirm that numbers in sum_range are stored as numbers, not text; use VALUE or paste-special “Add 0” to coerce if needed.
– For case sensitive searches, use SUMPRODUCT with FIND because SUMIF and SEARCH are case-insensitive.

Edge cases:

– Cells that contain only an asterisk and nothing else will still be included because the pattern matches.
– If you need to exclude formulas that return empty strings, wrap the pattern inside IF(len()>0,…).
– If the dataset includes question marks (?) that you also want to treat literally, escape them with ~? in similar fashion.

Step-by-Step Examples

Example 1: Basic Scenario

Imagine a small parts list where an asterisk marks discontinued items. Column A holds the SKU, column B holds the description, and column C holds the inventory value in dollars:

Goal: Sum only the values for discontinued items (those whose SKU contains an asterisk).

1. Select your output cell – say E2 for the result.
2. Enter the SUMIF formula:

=SUMIF(A2:A6,"*"&"~*"&"*",C2:C6)

3. Press Enter – Excel returns 2,350, which is the sum of 950 (P-102*) + 1,400 (P-104*).

Why it works: The criteria "*"&"~*"&"*" expands to *~** during calculation. The first and last asterisks act as wildcards, matching any number of characters before or after the literal asterisk. The tilde (~) forces Excel to read the middle asterisk literally, so only cells that genuinely include the star are counted.

Variations:

– If the asterisk appears at the end only, you could simplify the criteria to "~*", but the general pattern is safer for mixed positions.
– If you want to exclude starred SKUs, wrap SUMIF inside a subtraction: =SUM(C2:C6)-SUMIF(…).
– To show which rows qualify, add a helper column with =ISNUMBER(SEARCH("~*",A2)) and filter by TRUE.

Troubleshooting tips:

– Result is zero? Verify you typed the tilde (~) before the middle * and that data actually contains an asterisk.
– Wrong sum? Check that criteria_range and sum_range align row-by-row; mismatched ranges lead to silent errors.

Example 2: Real-World Application

Scenario: A national retail chain uses branch codes like “TX01*”, “NY03*Promo”, or “CA02” in column A to signal special promotions. Sales amounts reside in column D. Management needs weekly totals for promotional sales only, plus a breakdown by region.

Data excerpt [A2:D50]:

Task A – Total promotional sales:

=SUMIF(A2:A50,"*"&"~*"&"*",D2:D50)

Task B – Regional breakdown (one formula, copy down):

Assume the unique region list sits in F2:F4 (South, West, East). In G2 enter:

=SUMIFS(D$2:D$50,A$2:A$50,"*"&"~*"&"*",B$2:B$50,F2)

Copy the formula to G3 and G4. Each cell now displays the promotional sales subtotal per region.

Business impact:

– Marketing can instantly see how effective the promotion is by region.
– Finance can reconcile promotional rebates without manual filters.
– Branch managers spot underperforming stores in real time.

Integration with other Excel features:

– Add a PivotTable using the helper column “PromoFlag” (TRUE/FALSE).
– Use slicers on Region and Week to update dashboards dynamically.
– Combine with conditional formatting to highlight branches whose promo share exceeds 30 percent of total sales.

Performance considerations:

SUMIFS remains lightning-fast even on 50,000+ rows. If you regularly exceed 1 million rows, consider moving data to Power Query or Power Pivot and using DAX, but the same tilde escape concept applies.

Example 3: Advanced Technique

Edge case: A financial institution exports transaction descriptions with multiple asterisks indicating different compliance flags. For example, “AMLCHK*”, “PAYROLL*”, “*FRAUD”. The number of asterisks, their positions, and the surrounding text vary wildly. The analyst must sum only the transaction amounts that contain at least two asterisks (double-flagged) while ignoring single-asterisk items.

Data setup:

Goal: Sum amounts where the description contains two or more asterisks.

Approach: COUNT the number of asterisks in each cell with SUBSTITUTE, evaluate ≥2, and then sum with SUMPRODUCT. SUMIF alone cannot count inside text, so SUMPRODUCT provides the flexibility.

Helper-free array formula (Microsoft 365 dynamic formula or confirm with Ctrl+Shift+Enter in legacy versions):

=SUMPRODUCT(
  (LEN(A2:A6)-LEN(SUBSTITUTE(A2:A6,"*","")))>=2,
  B2:B6
)

Explanation:

1. LEN(A2:A6) returns the character length of each cell.
2. SUBSTITUTE(A2:A6,"*","") strips all asterisks, then LEN of that result finds the length without asterisks.
3. Subtracting the two lengths yields the count of asterisks per cell.
4. Check whether the count ≥2; this produces an array of TRUE/FALSE.
5. SUMPRODUCT coerces TRUE to 1, FALSE to 0, multiplies by amounts, and sums them.

Result: 11,400 (5,000 + 6,400). This advanced method handles multi-asterisk criteria, works irrespective of position, and remains performant on medium datasets. For millions of rows, consider a Power Query step to add an AsteriskCount column, filter, and load to the data model.

Professional tips:

– Add error handling: wrap SUBSTITUTE in IFERROR to treat blank cells gracefully.
– Cache the LEN(A2:A6) result in a helper column to improve speed on massive sheets.

Tips and Best Practices

1. Use Named Ranges – Assign names like PromoCodes or Amounts to your ranges to make formulas read =SUMIF(PromoCodes,"*~**",Amounts), boosting clarity.
2. Lock Ranges with Absolute References – When copying SUMIFS, anchor ranges with `

How to Sum If Cells Contain An Asterisk in Excel

Why This Task Matters in Excel

In thousands of workbooks created every day, a single asterisk (*) can completely change the meaning of a code, the validity of a part number, or the priority of a customer record. Manufacturers use asterisks in SKU numbers to flag obsolete stock, service organizations append an asterisk to ticket IDs that require escalation, and finance teams mark one-off journal entries with an asterisk so they can be reversed later. When you need to add up only the rows that include that tiny symbol, a simple SUM function is not enough—you must tell Excel to look for the asterisk and act on it.

The challenge is twofold. First, the asterisk is not an ordinary character in Excel’s lookup engine—it is a wildcard. When it appears in a criteria argument, Excel normally interprets it as “any sequence of characters.” That means the act of looking for a literal asterisk demands special handling. Second, the location of the asterisk inside the text string is rarely consistent. Sometimes it appears at the beginning, other times in the middle or at the end, and occasionally multiple asterisks are scattered throughout the same cell. Consequently, the formula needs to be flexible enough to “see” the asterisk wherever it resides.

Being able to sum only the rows that contain an asterisk unlocks cleaner reporting and faster decision-making. Inventory managers can instantaneously value obsolete stock, financial controllers can reconcile exceptional entries, and analysts can create dynamic dashboards that isolate or exclude flagged items with a single change of criteria. Without this skill, teams resort to time-consuming manual filters, error-prone copy-pastes, or VBA scripts that few people understand. Mastering it also sharpens your broader wildcard knowledge, which applies to filtering, conditional formatting, Power Query, and even database queries. In short, knowing how to sum if cells contain an asterisk is a foundational Excel competency that prevents costly mistakes and elevates the sophistication of your data workflows.

Best Excel Approach

For most situations, the simplest, fastest, and most transparent approach is to combine the SUMIF or SUMIFS function with the tilde (~) escape character. The tilde tells Excel to treat the next character literally, overriding its default wildcard behavior. By surrounding the tilde-escaped asterisk with regular wildcard asterisks, you create a flexible pattern that finds the asterisk anywhere in the cell.

Syntax for a single-criterion sum (SUMIF):

CODE_BLOCK_0

Syntax for multi-criteria sums (SUMIFS):

CODE_BLOCK_1

Why this is the best default:

1. Performance – SUMIF/SUMIFS is optimized in the Excel calculation engine and remains fast even on hundreds of thousands of rows.
2. Simplicity – One concise formula is easier to audit than nested array formulas.
3. Compatibility – Works in every modern Excel version: desktop, Mac, Excel Online, Microsoft 365, and even older perpetual licenses back to Excel 2007.
4. Dynamic – Changes automatically when you edit underlying data; no helper columns required.
5. Readability – Business users recognize the SUMIF family quickly in shared workbooks.

Reserve alternative methods—such as SUMPRODUCT with SEARCH/FIND, FILTER with SUM, or array formulas—for edge cases where you must ignore case sensitivity, include multiple patterns, or perform extra calculations at the same time.

Parameters and Inputs

To deploy SUMIF or SUMIFS successfully, you need to understand each argument and prepare your data accordingly:

• criteria_range
  – A single-column or single-row range that contains the text strings you want to test.
  – Data type: text (although numbers formatted as text also qualify).
  – Size must match sum_range exactly (number of rows and columns).

• \"\"&\"~\"&\"*\"
  – A concatenated text string that says: any characters before the literal asterisk, the literal asterisk itself, and any characters after it.
  – The leading and trailing asterisks act as wildcards; the tilde (~) escapes the middle asterisk so it is treated literally.

• sum_range
  – The numeric range containing values to add.
  – Must be the same size and shape as criteria_range; otherwise SUMIF returns incorrect results or a #VALUE! error.

• Optional additional criteria pairs (SUMIFS only)
  – Provide more range, criteria pairs to narrow the sum.
  – Each additional range must be the same shape as sum_range.

Data preparation tips:

– Remove excess spaces with TRIM or CLEAN before applying formulas; hidden spaces can cause unexpected mismatches.
– Confirm that numbers in sum_range are stored as numbers, not text; use VALUE or paste-special “Add 0” to coerce if needed.
– For case sensitive searches, use SUMPRODUCT with FIND because SUMIF and SEARCH are case-insensitive.

Edge cases:

– Cells that contain only an asterisk and nothing else will still be included because the pattern matches.
– If you need to exclude formulas that return empty strings, wrap the pattern inside IF(len()>0,…).
– If the dataset includes question marks (?) that you also want to treat literally, escape them with ~? in similar fashion.

Step-by-Step Examples

Example 1: Basic Scenario

Imagine a small parts list where an asterisk marks discontinued items. Column A holds the SKU, column B holds the description, and column C holds the inventory value in dollars:

Goal: Sum only the values for discontinued items (those whose SKU contains an asterisk).

1. Select your output cell – say E2 for the result.
2. Enter the SUMIF formula:

CODE_BLOCK_2

3. Press Enter – Excel returns 2,350, which is the sum of 950 (P-102*) + 1,400 (P-104*).

Why it works: The criteria "*"&"~*"&"*" expands to *~** during calculation. The first and last asterisks act as wildcards, matching any number of characters before or after the literal asterisk. The tilde (~) forces Excel to read the middle asterisk literally, so only cells that genuinely include the star are counted.

Variations:

– If the asterisk appears at the end only, you could simplify the criteria to "~*", but the general pattern is safer for mixed positions.
– If you want to exclude starred SKUs, wrap SUMIF inside a subtraction: =SUM(C2:C6)-SUMIF(…).
– To show which rows qualify, add a helper column with =ISNUMBER(SEARCH("~*",A2)) and filter by TRUE.

Troubleshooting tips:

– Result is zero? Verify you typed the tilde (~) before the middle * and that data actually contains an asterisk.
– Wrong sum? Check that criteria_range and sum_range align row-by-row; mismatched ranges lead to silent errors.

Example 2: Real-World Application

Scenario: A national retail chain uses branch codes like “TX01*”, “NY03*Promo”, or “CA02” in column A to signal special promotions. Sales amounts reside in column D. Management needs weekly totals for promotional sales only, plus a breakdown by region.

Data excerpt [A2:D50]:

Task A – Total promotional sales:

CODE_BLOCK_3

Task B – Regional breakdown (one formula, copy down):

Assume the unique region list sits in F2:F4 (South, West, East). In G2 enter:

CODE_BLOCK_4

Copy the formula to G3 and G4. Each cell now displays the promotional sales subtotal per region.

Business impact:

– Marketing can instantly see how effective the promotion is by region.
– Finance can reconcile promotional rebates without manual filters.
– Branch managers spot underperforming stores in real time.

Integration with other Excel features:

– Add a PivotTable using the helper column “PromoFlag” (TRUE/FALSE).
– Use slicers on Region and Week to update dashboards dynamically.
– Combine with conditional formatting to highlight branches whose promo share exceeds 30 percent of total sales.

Performance considerations:

SUMIFS remains lightning-fast even on 50,000+ rows. If you regularly exceed 1 million rows, consider moving data to Power Query or Power Pivot and using DAX, but the same tilde escape concept applies.

Example 3: Advanced Technique

Edge case: A financial institution exports transaction descriptions with multiple asterisks indicating different compliance flags. For example, “AMLCHK*”, “PAYROLL*”, “*FRAUD”. The number of asterisks, their positions, and the surrounding text vary wildly. The analyst must sum only the transaction amounts that contain at least two asterisks (double-flagged) while ignoring single-asterisk items.

Data setup:

Goal: Sum amounts where the description contains two or more asterisks.

Approach: COUNT the number of asterisks in each cell with SUBSTITUTE, evaluate ≥2, and then sum with SUMPRODUCT. SUMIF alone cannot count inside text, so SUMPRODUCT provides the flexibility.

Helper-free array formula (Microsoft 365 dynamic formula or confirm with Ctrl+Shift+Enter in legacy versions):

CODE_BLOCK_5

Explanation:

1. LEN(A2:A6) returns the character length of each cell.
2. SUBSTITUTE(A2:A6,"*","") strips all asterisks, then LEN of that result finds the length without asterisks.
3. Subtracting the two lengths yields the count of asterisks per cell.
4. Check whether the count ≥2; this produces an array of TRUE/FALSE.
5. SUMPRODUCT coerces TRUE to 1, FALSE to 0, multiplies by amounts, and sums them.

Result: 11,400 (5,000 + 6,400). This advanced method handles multi-asterisk criteria, works irrespective of position, and remains performant on medium datasets. For millions of rows, consider a Power Query step to add an AsteriskCount column, filter, and load to the data model.

Professional tips:

– Add error handling: wrap SUBSTITUTE in IFERROR to treat blank cells gracefully.
– Cache the LEN(A2:A6) result in a helper column to improve speed on massive sheets.

Tips and Best Practices

1. Use Named Ranges – Assign names like PromoCodes or Amounts to your ranges to make formulas read =SUMIF(PromoCodes,"*~**",Amounts), boosting clarity.
2. Lock Ranges with Absolute References – When copying SUMIFS, anchor ranges with (e.g., $A$2:$A$5000) to prevent accidental shifts that break totals.
3. Document the Tilde – Add a comment or note explaining ~* so colleagues immediately know you’re escaping a wildcard.
4. Combine with Tables – Converting data to an Excel Table auto-expands ranges and makes formulas self-updating: =SUMIF(tbl[Code],"*~**",tbl[Value]).
5. Minimize Volatile Functions – Prefer SUMIF/SUMIFS over SUMPRODUCT when possible; SUMPRODUCT recalculates every time, which can slow large workbooks.
6. Validate with Filters – After writing the formula, apply a text filter “Contains *” to visually confirm which rows Excel is summing.

Common Mistakes to Avoid

1. Forgetting the Tilde (~)
   – Error: =SUMIF(A:A,"**",C:C) returns the sum of all rows because Excel reads ** as wildcards.
   – Fix: Insert the tilde: "*~*".

2. Mismatched Range Sizes
   – Error: criteria_range has 500 rows, sum_range has 600, resulting in incorrect totals.
   – Fix: Ensure both ranges are identical in size or use structured references in an Excel Table.

3. Text-Numbers Confusion
   – Error: Amounts imported as text produce a result of zero.
   – Fix: Convert text numbers with VALUE or multiply by 1 inside SUMPRODUCT.

4. Rigid Criteria Pattern
   – Error: Searching for "~*", which only matches an asterisk at the beginning of the string, missing Product*End.
   – Fix: Use leading and trailing wildcards "*~*" unless position is known.

5. Overusing Array Formulas
   – Error: Jumping straight to SUMPRODUCT for every case leads to sluggish files.
   – Fix: Start with SUMIF/SUMIFS; escalate to array methods only when essential.

Alternative Methods

Choose SUMIF/SUMIFS first. Switch to SUMPRODUCT when you need counts, case sensitivity, or multi-asterisk thresholds. Use FILTER for modern dynamic reports, and offload to Power Query or DAX when file size or performance becomes an issue.

FAQ

When should I use this approach?

Use the tilde-escaped SUMIF when your dataset contains a literal asterisk character that marks special items and you need a quick, maintenance-free total. Typical scenarios include discontinued SKUs, promo codes, or flags in log files.

Can this work across multiple sheets?

Yes. Reference ranges with sheet names:

=SUMIF(OldData!A:A,"*~*",OldData!C:C)

If sheets have different structures, consolidate them into a single Table or Power Query stack first to avoid range mismatches.

What are the limitations?

SUMIF/SUMIFS cannot count how many asterisks appear; it only checks presence. They are also case-insensitive. For case sensitivity or multi-asterisk thresholds, use FIND with SUMPRODUCT or add a helper column.

How do I handle errors?

Wrap formulas with IFERROR to suppress #VALUE! in partial datasets:

=IFERROR(SUMIF(A:A,"*~*",C:C),0)

Use data validation to prevent accidental entry of numbers stored as text, which can silently skew totals.

Does this work in older Excel versions?

Absolutely. The tilde escape mechanism has existed since Excel 95. SUMIF appeared in Excel 97, and SUMIFS in Excel 2007. Even users on legacy versions can apply the same logic.

What about performance with large datasets?

SUMIF/SUMIFS is highly optimized and can handle hundreds of thousands of rows with negligible delay. If calculation time exceeds a few seconds, consider:

1. Turning off automatic calculation while editing.
2. Converting to an Excel Table so ranges don’t point to entire columns.
3. Moving data to Power Pivot and writing an equivalent DAX measure using CONTAINSSTRING.

Conclusion

Being able to sum only the rows that contain an asterisk empowers you to spotlight exceptions, value obsolete stock, and track promotions with pinpoint accuracy. The tilde-escaped SUMIF/SUMIFS formula is the simplest and fastest way to accomplish the task, while advanced options like SUMPRODUCT, FILTER, or Power Query cover complex edge cases. Mastering this technique strengthens your wildcard fluency, makes your reports more reliable, and integrates seamlessly with broader Excel workflows from conditional formatting to dynamic dashboards. Practice on your own data today, experiment with alternative methods, and add this powerful tool to your Excel repertoire.