SUMIF in Excel: Match Cells That Contain Specific Text

Formulas & functions · Updated November 18, 2026

Yes. SUMIF can add values when a cell contains particular text: with descriptions in A2:A7 and amounts in B2:B7, use =SUMIF(A2:A7,"*paper*",B2:B7). The asterisks are wildcards, so this example sums amounts for descriptions that include “paper” anywhere in the cell.

How SUMIF matches text

SUMIF has the syntax SUMIF(range, criteria, [sum_range]). The first range contains the values Excel checks, criteria tells Excel what to look for, and the optional sum_range contains the numbers to add. If you leave out sum_range, Excel adds values in the first range instead. For a text search, criteria can use wildcard characters to match more than an exact cell value.

An asterisk (*) represents any number of characters, including no characters. Put one before and after a search word to find that word anywhere in a cell. For example, "*paper*" matches “Paper Clips,” “Northwind Paper,” and “Blue Paper Ltd.” It does not require the cell to contain only the search word.

A question mark (?) represents exactly one character. For instance, a criterion like "A?C" can match “ABC” or “A-C,” but not “AC” or “ABBC.” Wildcards are useful when a value varies in a known way, but an asterisk is the usual choice for a contains-text search.

SUMIF text matching is not case-sensitive. The criterion “paper” also finds “Paper” or “PAPER.” If capitalization matters, SUMIF alone is not the right function for a case-sensitive test; a formula using EXACT with SUMPRODUCT is one possible alternative.

Set up a contains-text example

Suppose the worksheet has the following descriptions, amounts, and regions. The descriptions are in A2:A7, amounts in B2:B7, and regions in C2:C7:

  • Northwind Paper — 120 — East
  • Paper Clips Co — 45 — West
  • Blue Paper Ltd — 80 — East
  • Notebook Depot — 60 — East
  • Paper & Ink — 35 — West
  • Blue Ink — 25 — East

With descriptions in A2:A7 and amounts in B2:B7, this formula adds all rows whose description contains “paper”: =SUMIF(A2:A7,"*paper*",B2:B7). The matching amounts are 120, 45, 80, and 35, for a result of 280. “Notebook Depot” and “Blue Ink” do not match, so their amounts are not included.

The order of the ranges matters. In this example, A2:A7 is the range to inspect and B2:B7 is the sum_range. Both cover six corresponding rows, so Excel can use each description to decide whether to add the amount on the same row. Keep these ranges aligned and the same size; otherwise, the results may not represent the records you intend to total.

Match text at the beginning or end

The location of the asterisk changes what the criterion means. Use an asterisk on both sides to find text anywhere in a cell. Put it only after the search text to match cells that begin with that text, or only before it to match cells that end with it.

  • Contains “paper”: "*paper*"
  • Begins with “Paper”: "Paper*"
  • Ends with “Paper”: "*Paper"

With descriptions in A2:A7 and amounts in B2:B7, =SUMIF(A2:A7,"Paper*",B2:B7) returns 80 for the sample data: “Paper Clips Co” contributes 45 and “Paper & Ink” contributes 35. The formula =SUMIF(A2:A7,"*Paper",B2:B7) returns 120 because only “Northwind Paper” ends with that word. The results illustrate why choosing the wildcard position is important.

Use a cell for the search text

Hard-coding a word in a formula is convenient for a one-off total. If you want to change the search term frequently, put it in a cell instead. For example, enter paper in E2, then use the cell as the text between two wildcards.

With descriptions in A2:A7, amounts in B2:B7, and the search word in E2, use =SUMIF(A2:A7,"*"&E2&"*",B2:B7). If E2 contains “paper,” the result is 280. Change E2 to “ink” and the formula recalculates to 60: “Paper & Ink” contributes 35 and “Blue Ink” contributes 25.

The ampersands join the wildcard characters to the cell value, forming the criterion Excel needs. The quotes around the asterisks are required because they are literal text in the criterion. This construction also works when the search term is returned by another formula. If E2 is blank, the criterion becomes two wildcards, which can match every text entry; guard against an accidental blank search cell if summing every row would be misleading.

Search for wildcard characters literally

Sometimes the asterisk or question mark is part of the text itself, rather than a wildcard you want Excel to interpret. Use a tilde (~) to escape a wildcard character. For example, ~* means a literal asterisk, and ~? means a literal question mark. A tilde that needs to be matched literally is represented by two tildes.

With product descriptions in A2:A7 and amounts in B2:B7, =SUMIF(A2:A7,"*~**",B2:B7) looks for a literal asterisk anywhere in a description: the first and last asterisks are wildcards, while ~* is the literal character. This is useful for labels or imported codes that actually contain symbols. Without the tilde, Excel treats an asterisk in the criterion as a wildcard, not as a character to search for.

Use SUMIFS for additional conditions

SUMIF handles one condition. If you need the description to contain text and also want to restrict the total by region, use SUMIFS, which accepts multiple criteria. Its argument order is different: the sum_range comes first, followed by a criteria range and criterion for each condition.

With descriptions in A2:A7, amounts in B2:B7, and regions in C2:C7, use =SUMIFS(B2:B7,A2:A7,"*paper*",C2:C7,"East") to total rows containing “paper” in the East. In the sample data, “Northwind Paper” contributes 120 and “Blue Paper Ltd” contributes 80, so the result is 200. “Paper Clips Co” and “Paper & Ink” contain the text but are in the West, so they are excluded.

SUMIFS is preferable to adding several separate SUMIF formulas when a single total must satisfy multiple conditions at once. For a simple contains-text total with just one condition, SUMIF is shorter and easier to read. Both functions can use wildcards in text criteria.

Common problems and checks

If a contains-text formula returns zero or an unexpected total, check the data and ranges before changing the wildcard pattern. These are common sources of trouble:

  • Wrong range order: The first range in SUMIF is the one Excel searches, not the one it sums. Put the amount range in the third argument when descriptions are in the first argument.
  • Misaligned ranges: Make sure each criteria cell corresponds to the amount on the same row. Use ranges that start and end on the intended rows.
  • Unexpected spaces or characters: A leading or trailing space in the search term can make the criterion miss the intended word. Data copied from websites or other systems can also include nonprinting characters. Inspect the cell contents or clean the source data.
  • Amounts stored as text: SUMIF adds numeric values, not text that merely looks like a number. If amounts were imported as text, convert them to numbers before relying on the total.
  • Blank search cell: A criterion built from a blank cell and two wildcards can match broadly. Check that the search cell contains the intended term.
  • Literal wildcard needed: Escape a literal asterisk or question mark with a tilde rather than expecting it to be treated as ordinary text.

When investigating a result, test the criterion against a few individual descriptions and confirm which rows should qualify. Also verify whether a cell contains the exact visible text you expect; a value can look correct while containing extra spaces. The issue is often the source data or the selected ranges, not a limitation in SUMIF.

Frequently Asked Questions

Can SUMIF search for text anywhere in a cell?
Yes. Put an asterisk before and after the word in the criterion, such as "*paper*". Those wildcards let Excel find the text at the beginning, middle, or end of a cell.

Is SUMIF case-sensitive?
No. SUMIF text criteria ignore capitalization, so a criterion for “paper” can match “PAPER.” For a case-sensitive total, use a different approach, such as combining EXACT and SUMPRODUCT.

Can SUMIF match part of a word?
Yes. A contains criterion can match a sequence within a longer word as well as a separate word. Searching for “art,” for example, may match “carton.” If that is not wanted, the source data or matching method must distinguish whole words from text fragments.

Why does SUMIF return zero even though the text looks like a match?
Check that the asterisks surround the search term, that the criteria range points to the text cells, and that the sum_range points to numeric values on the same rows. Also check for extra spaces, hidden characters, or amounts stored as text.

Can I use SUMIF when the search term is in another cell?
Yes. Join wildcards around the cell reference with ampersands, as in =SUMIF(A2:A7,"*"&E2&"*",B2:B7) when descriptions are in A2:A7, amounts in B2:B7, and the term is in E2. Changing E2 changes the search without editing the formula.

Related

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.