Excel Wildcard SUMIF Formula Examples That Match Text

To total values for text that contains a word, use SUMIF with asterisks around the word: with product names in A2:A8 and amounts in B2:B8, =SUMIF(A2:A8,"*blue*",B2:B8) returns 26 for the sample data below. Use * for any number of characters, ? for exactly one character, and ~ when you need to match a wildcard symbol itself.

How wildcard SUMIF works

SUMIF adds numbers whose corresponding cells meet one condition. Its arguments are the cells to test, the condition, and—optionally—the cells to add. In the opening example, Excel checks each product name in A2:A8 against the condition *blue*, then adds the value in the same row of B2:B8 whenever the name contains blue. The matching names are Pen blue (8), Marker blue (4), Blue folder (9), and Pen blue (5), so the total is 26.

The general structure is SUMIF(range, criteria, [sum_range]). The square brackets in this description mean that sum_range is optional; do not type the brackets into a formula. If you leave it out, Excel adds the cells in range itself. For a text-matching total, range is usually a column of labels and sum_range is a parallel column of numbers. The ranges should cover corresponding rows and have the same shape.

In Microsoft Excel for Microsoft 365 on Windows, enter the formula in a cell outside the source data and press Enter. The same formula syntax works in Excel for Mac and Excel for the web. Depending on regional settings, Excel may require semicolons instead of commas as argument separators; use the separator Excel expects in your installation.

Examples for text at the beginning, middle, or end

These examples use the product names and amounts described above: A2:A8 contains Paper clips, Pen blue, Pen black, Pencil HB, Marker blue, Blue folder, and Pen blue; B2:B8 contains 12, 8, 11, 6, 4, 9, and 5 in that order.

Contains blue: with names in A2:A8 and amounts in B2:B8, =SUMIF(A2:A8,"*blue*",B2:B8) returns 26. The asterisk on each side allows any characters before and after blue.
Begins with Pen: with names in A2:A8 and amounts in B2:B8, =SUMIF(A2:A8,"Pen*",B2:B8) returns 24: 8 + 11 + 5. The asterisk permits any continuation after Pen, including no additional characters.
Ends with blue: with names in A2:A8 and amounts in B2:B8, =SUMIF(A2:A8,"*blue",B2:B8) returns 12, from Pen blue (8) and Marker blue (4). Blue folder does not match because blue is at the start, not the end.
Matches an exact label: with names in A2:A8 and amounts in B2:B8, =SUMIF(A2:A8,"Pen blue",B2:B8) returns 13. Without wildcards, SUMIF looks for the whole matching cell value, rather than a phrase embedded in a longer label.

These patterns are useful for product descriptions, department names, codes, and other text labels. They do not require the wildcard to represent a fixed number of characters unless you use question marks. For example, *Pen* would match a label with Pen anywhere in it, including Pencil, so choose the shortest pattern that expresses what you intend.

Using the question mark for one character

A question mark stands for exactly one character. It can help group codes whose length is consistent while one position varies. For instance, suppose A2:A6 contains SKU-12, SKU-7, SKU-A4, SKU-123, and SKU-34, while B2:B6 contains 5, 3, 7, 9, and 4. The pattern SKU-?? accepts exactly two characters after the hyphen. With those code and amount ranges, =SUMIF(A2:A6,"SKU-??",B2:B6) returns 16: it matches SKU-12 (5), SKU-A4 (7), and SKU-34 (4). SKU-7 has only one character after the hyphen, and SKU-123 has three.

The question mark does not mean “one digit”; it can match any single character, including a letter or punctuation mark. In that example, SKU-A4 qualifies. If a criterion must validate that a position is a number, wildcard matching alone is not a reliable way to enforce that rule. Use a separate data-validation or parsing approach when the character type matters.

Put the wildcard criterion in a cell

A criterion can be assembled from text in a worksheet cell. This is helpful when someone needs to change the search term without editing the formula. Suppose D2 contains blue, A2:A8 contains product names, and B2:B8 contains amounts. With those ranges and the search term in D2, =SUMIF(A2:A8,"*"&D2&"*",B2:B8) returns 26 for the sample data. The ampersands join the wildcard characters to the cell value, making the effective criterion *blue*.

Keep the quotation marks around the wildcard symbols, but not around the cell reference. If you write "*D2*", Excel treats D2 as literal text rather than reading the value in that cell. If D2 might be blank, note that the assembled criterion becomes two asterisks, which can match virtually any text entry and produce a broad total. You can prevent an accidental all-items result by requiring a nonblank search term before using the formula, or by displaying a prompt when D2 is empty.

Match a literal asterisk or question mark

Because asterisk and question mark have special meanings, use a tilde to search for either character as ordinary text. For example, suppose A2:A5 contains Plan*, Plan A, Plan?, and Plan*, and B2:B5 contains 10, 20, 30, and 5. With those labels in A2:A5 and values in B2:B5, =SUMIF(A2:A5,"Plan~*",B2:B5) returns 15. The tilde tells Excel to treat the following asterisk literally, so both Plan* entries qualify but Plan A and Plan? do not.

Likewise, use ~? in a criterion to match a literal question mark, and ~~ to match a literal tilde. This escaping matters in product names or imported text that actually contains symbols. A criterion such as Plan* without the tilde means Plan followed by any number of characters; it is not a request for the exact label Plan*.

Combine wildcard matching with SUMIFS

SUMIF accepts one criterion. Use SUMIFS when the total must satisfy multiple conditions, such as a region and a text pattern. SUMIFS puts the sum range first, followed by pairs of criteria ranges and criteria. Suppose A2:A7 contains East, East, West, East, East, West; B2:B7 contains Blue, Dark blue, Blue, Red, Blue, Dark blue; and C2:C7 contains 10, 7, 6, 4, 5, 8. With region in A2:A7, descriptions in B2:B7, and amounts in C2:C7, =SUMIFS(C2:C7,A2:A7,"East",B2:B7,"*blue*") returns 22: the qualifying East rows contribute 10 + 7 + 5.

This is useful when a text match is only one part of the business rule. Each additional condition needs its own range-and-criterion pair, and all criteria must be true for a row to be included. SUMIFS supports the same asterisk, question-mark, and tilde wildcard behavior for text criteria. Check the argument order when moving from SUMIF: the sum range is first in SUMIFS, but last in SUMIF.

Common problems and checks

The formula returns zero: check spelling, spaces, and whether the search text is actually in the criterion range. Imported data can contain leading or trailing spaces or nonbreaking spaces that are difficult to see. A wildcard cannot make different characters identical.
The total is too large: a broad pattern such as *Pen* also matches words such as Pencil. Review the rows that fit the pattern and narrow it, for example by using a prefix or suffix pattern when appropriate.
The wrong values are added: verify that sum_range lines up with range row by row. If labels are in A2:A100 but amounts are accidentally taken from B1:B99, the paired values are offset. Ensure both ranges cover the intended records.
Numbers or dates do not match as expected: wildcards are for text matching, not a substitute for numeric comparisons. A real Excel date is stored as a number, even if it displays as text such as Jan 2026. For numeric and date criteria, use comparison criteria or date boundaries rather than searching the displayed format with asterisks.
Case seems to be ignored: SUMIF wildcard matching is not case-sensitive. A criterion containing blue can match Blue as well. If case-sensitive totals are essential, SUMIF alone does not provide that distinction; use a formula designed around case-sensitive text comparison instead.

Also confirm that the cells to be added contain numbers rather than number-like text. Excel may not treat text entries as numeric values in the same way as true numbers, and source data imported from another system can mix the two. If results remain surprising, inspect a few matching rows directly and check the underlying cell contents, not only their displayed formatting.

When to choose another approach

SUMIF is a good fit when one wildcard condition determines which numbers to add. Choose SUMIFS for two or more conditions. Use COUNTIF or COUNTIFS when the goal is to count matching records rather than total a numeric column. For exact numeric ranges, comparisons such as greater than or less than are clearer than wildcard patterns. For a case-sensitive test, a dedicated case-sensitive formula approach is needed because SUMIF does not distinguish uppercase from lowercase.

Wildcards also do not alter the source data or permanently filter the worksheet; they only affect which rows the formula includes. If you need to see the matching records as well as their total, use Excel’s filter tools or a separate extraction formula in a version that supports it. Keep the SUMIF formula when a compact, automatically updating total is the main requirement.

Frequently Asked Questions

Can SUMIF search for text anywhere in a cell?
Yes. Put an asterisk before and after the search text, as in "*blue*". That pattern matches cells containing blue at the beginning, middle, or end. It is not case-sensitive.

What does the asterisk mean in an Excel SUMIF formula?
An asterisk matches zero or more characters. For example, Pen* matches Pen, Pen blue, and Pencil, while *Pen matches text that ends in Pen. Add a tilde before an asterisk if you need to match the symbol itself.

Can I use SUMIF with a wildcard stored in another cell?
Yes. Join the wildcard characters to the cell reference with ampersands, such as "*"&D2&"*". Make sure D2 contains the search text, and consider what should happen if that cell is blank.

Does SUMIF distinguish uppercase and lowercase?
No. SUMIF is case-insensitive, so a criterion for blue can match Blue. Use a case-sensitive method if capitalization must determine which rows qualify.

Why does my wildcard SUMIF formula return zero?
Common causes include a misspelled criterion, mismatched ranges, hidden spaces, or numbers stored as text. Also check whether the criterion is searching for a literal wildcard character: a tilde is required to escape an asterisk or question mark.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.