Free Excel workbook
Get the SUMIFS Partial Match Workbook
Download the ready-to-use workbook and practice the exact formulas from the tutorial.
- Practice SUMIFS with partial text matches using wildcards
- Test contains, starts with, ends with, and fixed-position examples already set up
- Includes the double-criteria challenge so you can check what you learned
Enter your email below for instant access.
Free. Instant access.
Learn how to use Excel to sum if cells contain specific text (partial text match).
Excel will allow you to perform filtered aggregations with the following functions:
- SUMIFS – sum values based on one or more criteria
- AVERAGEIFS – average based on one or more criteria
- COUNTIFS – count based on one or more criteria
- MAXIFS – find the largest value in a set based on one or more criteria
- MINIFS – find the smallest value in a set based on one or more criteria
Although these are fantastic functions, most users are under the impression that the criteria must match entirely in order to be included in the aggregation. Although, this is how these functions are typically used, this is not the case.
I have covered the mechanics of filtered aggregations (SUMIFS, COUNTIFS, and AVERAGEIFS) in a previous post.
I have also covered a technique to sum values between two dates using the SUMIFS and EOMONTH functions. Click this link to review.
What Are Wildcards in Excel?
Wildcards stand for “any characters,” making them handy for searching multiple items with a similar pattern.
There are two main wildcards:
- Asterisk (*) – Matches any number of characters.
- Example 1:
Exc*(matches “Excel” or “Exception.”) - Example 2:
*el(matches “Excel” or “Travel.”) - Example 3:
*xce*(any text with “exc” anywhere in the text; beginning, middle, or end) - Example 4:
Exc*el(any text starting with “Exc”, ending with “el”, and any character in between)
- Example 1:
- Question Mark (?) – Matches exactly one character. This wildcard is useful when you must locate something in a specific position within a set of text.
- Example 1:
??cel(The first and second characters can be anything, but the third through fifth characters must be “cel”) - Example 2: Ex?el (matches “Excel” or “Exxel,” but not “Exceptional.”)
- Example 1:
💡 Wildcard Cheat Sheet
| Criteria | Formula Logic | Matches… |
| Starts With | "Text*" | Any text starting with “Text” |
| Ends With | "*Text" | Any text ending with “Text” |
| Contains | "*Text*" | Any text where “Text” appears anywhere |
How Do You Use SUMIFS with Wildcards? 5 Practical Examples
Here are practical examples to help you master SUMIFS with wildcards.
Examine the sample data set.
How to Sum Values That Contain Specific Text
We want to sum the “Quantity Sold” where “Company ID” contains “AT” anywhere.
Select cell G6 and enter the following formula:
=SUMIFS( $C$4:$C$18, $A$4:$A$18, F6 )This returns zero because SUMIFS is looking for an exact match to “AT”, not cells containing “AT”.
We want the formula to be more forgiving.
Update the formula in cell G6 with the following modification:
=SUMIFS( $C$4:$C$18, $A$4:$A$18, ”*” & F6 & ”*” )By placing an asterisk both in front and following the text in cell F6, we have told the SumIFs that the characters “AT” can occur anywhere within the “Company ID”.
Because we are searching for text, we must enclose the asterisks (*) in double-quotes and concatenate the components with ampersands (&).
Fill the formula from cell G6 down to cell G9.
How to Sum Values That Start With Specific Text
Now, let’s sum the “Quantity Sold” where “Company ID” begins with “10.”
Select cell G12 and enter the following formula:
=SUMIFS( $C$4:$C$18, $A$4:$A$18, F12 & ”*” )
How to Sum Values That End With Specific Text
To sum “Quantity Sold” where “Company ID” ends with “10”:
Select cell G13 and enter the following formula:
=SUMIFS( $C$4:$C$18, $A$4:$A$18, ”*” & F13 )
How to Sum Values by Character Position
Let’s sum values where “Company ID” has “10” in the 5th and 6th positions.
Select cell G14 and enter the following formula:
=SUMIFS( $C$4:$C$18, $A$4:$A$18, ”????” & F14 & ”*” )Here, the question marks (?) ensure the first four characters are skipped, while the asterisk (*) accounts for characters after position six.
How to Sum Values With Both Starts-With and Ends-With Criteria
To sum “Quantity Sold” where “Company ID” starts with “AT” and ends with “30”:
We will use the text functions LEFT and RIGHT to assist with the text search.
Select cell G17 and enter the following formula:
=SUMIFS( $C$4:$C$18, $A$4:$A$18, LEFT(F17,2) & ”*” &RIGHT(F17,2) )In the example in cell G17, it would be as if we were searching for “AT*30”.
Fill the formula from cell G17 down to cell G19.
How Do You Add a Second Condition to a Partial Match SUM?
What if you want to sum values based on “Company ID” and another condition, like “Customer”?
Select cell H23 and enter the following formula:
=SUMIFS( $C$4:$C$18, $A$4:$A$18, F23 & ”*”, $B$4:$B$18, G23 )
Fill the formula from cell H23 down to cell H26.
Need more complex criteria beyond SUMIFS? See our guide to FILTER with multiple criteria for a more flexible alternative in Microsoft 365.
Can You Use SUMIF with Wildcards?
Yes. SUMIF supports the same wildcards as SUMIFS: asterisk (*) for any characters and question mark (?) for a single character.
The syntax difference is argument order. SUMIF puts the sum_range last, while SUMIFS puts it first:
=SUMIF(A2:A100, "*AT*", C2:C100)This sums C2:C100 where A2:A100 contains “AT” anywhere.
Use SUMIF for a single condition. Use SUMIFS when you need two or more criteria. Everything else in this guide works identically with either function.
How Do You Sum Cells That Literally Contain an Asterisk or Question Mark?
If your text actually contains an asterisk or question mark as a real character, wildcards will cause false matches. Use the tilde (~) to escape them:
=SUMIFS(C2:C100, A2:A100, "~*discount")The tilde (~) before the asterisk tells Excel to treat it as a literal asterisk character, not as a wildcard. Without the tilde, “*discount” would match anything ending in “discount”.
The same rule applies to question marks:
=SUMIFS(C2:C100, A2:A100, "Question~?")This matches cells containing the literal text “Question?” at the end.
How Do You Make SUMIFS Case-Sensitive?
SUMIF and SUMIFS are case-insensitive by default. The criteria “at” matches “AT”, “at”, “At”, and “aT”.
For case-sensitive matching, use SUMPRODUCT with FIND or EXACT:
=SUMPRODUCT(ISNUMBER(FIND("AT", A2:A100)) * C2:C100)FIND is case-sensitive (unlike SEARCH, which is not). ISNUMBER converts the position result into TRUE/FALSE, which SUMPRODUCT multiplies by C2:C100 to sum only the matching rows.
For whole-cell case-sensitive matching, use EXACT:
=SUMPRODUCT(EXACT(A2:A100, "AT") * C2:C100)Key Takeaways
- Use asterisks (*) to match any characters and question marks (?) to match specific positions.
- Combine wildcards with SUMIFS to analyze patterns in your data efficiently.
- Experiment with these formulas in the practice workbook to reinforce your skills!
Can You Use REGEX Patterns Instead of Wildcards?
SUMIFS itself does not support regex. But in Microsoft 365, you can combine REGEXTEST with SUMPRODUCT to sum by regex pattern:
=SUMPRODUCT(REGEXTEST(A2:A100, "^AT.*30$") * C2:C100)REGEXTEST returns TRUE or FALSE for each cell. Multiplying by C2:C100 sums only the rows where the pattern matches. The example above sums values where Company ID starts with “AT” and ends with “30”.
Use regex when you need OR logic, character classes, or precise position matching that wildcards cannot express.
For simple “contains”, “starts with”, and “ends with” patterns, SUMIFS with wildcards is shorter and faster.
For the full regex syntax, see our guide to REGEX functions in Excel.
Frequently Asked Questions
How do I sum cells that contain specific text in Excel?
Use SUMIFS with the asterisk (*) wildcard to match text anywhere in a cell. The formula =SUMIFS(C2:C100, A2:A100, "*apple*") sums values in C2:C100 where column A contains the word apple anywhere in the cell. Wrap the search term in asterisks and enclose the whole criteria in double quotes.
What is the difference between * and ? wildcards in Excel?
The asterisk (*) matches any number of characters, including none. The question mark (?) matches exactly one character in a specific position. Use * for contains, starts with, and ends with patterns. Use ? when you need to skip a specific number of characters before or after a match.
Can I use wildcards with a cell reference in SUMIFS?
Yes. Concatenate the wildcard with the cell reference using the ampersand (&). For example, =SUMIFS(C2:C100, A2:A100, "*" & E2 & "*") uses the search term in cell E2 wrapped in asterisks. This lets you change the search term without editing the formula.
Does SUMIF work with wildcards like SUMIFS?
Yes. SUMIF supports the same * and ? wildcards. The argument order differs: SUMIF puts sum_range last, SUMIFS puts it first. Example: =SUMIF(A2:A100, "*AT*", C2:C100). Use SUMIF for a single condition and SUMIFS for multiple criteria.
How do I make SUMIFS case-sensitive?
SUMIFS is case-insensitive by default. For case-sensitive matching, use SUMPRODUCT with FIND: =SUMPRODUCT(ISNUMBER(FIND("AT", A2:A100)) * C2:C100). FIND is case-sensitive (unlike SEARCH).
Why is my SUMIFS returning zero?
The most common cause is that SUMIFS is looking for an exact match when you need a partial match. Wrap your search term in asterisks: "*text*" instead of just "text". Also check for hidden spaces (use TRIM), smart quotes vs straight quotes, and that your criteria_range and sum_range have the same number of cells.
Download the Practice Workbook
FREE EXCEL WORKBOOK
Master the “Double Criteria” Challenge
Download the practice file and try the Double Criteria challenge on the results sheet.
- All 5 SUMIFS wildcard formulas ready to copy
- The Double Criteria challenge with solution sheet
- Sample data with 15 Company IDs to test against
Free. Instant access. No spam.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
