SUMIF Excel: Sum Cells Containing Specific Text

To sum values when a related cell contains particular text, use SUMIF with asterisks as wildcards. With descriptions in A2:A100, amounts in B2:B100, and the text to find in E2, enter =SUMIF(A2:A100,"*"&E2&"*",B2:B100). Excel adds the amounts for rows where the description contains the text in E2, even if it appears in the middle of a longer phrase.

How SUMIF finds text within a cell

SUMIF checks each cell in a criteria range against a criterion, then adds the corresponding value from a sum range when there is a match. Its syntax is SUMIF(range, criteria, [sum_range]). The square brackets indicate that the final argument is optional; they are not typed into the formula.

An asterisk (*) is Excel’s wildcard for any number of characters, including no characters. Put one before and after the search text to allow anything on either side. The criterion "*apple*", for example, can match “apple,” “green apple,” and “apple juice.” The match is not restricted to the beginning or end of a cell.

In the opening formula, A2:A100 is the range Excel checks, E2 supplies the search phrase, and B2:B100 contains the numbers to add. The ampersands join the wildcard characters to the value in E2. If E2 contains apple, Excel effectively searches for text matching *apple*. Keep the criteria range and sum range aligned row by row so each match adds the amount from the same record.

A worked example with a partial match

Suppose A2:A6 contains product descriptions and B2:B6 contains sales amounts:

Description	Sales
Red apple	12
Green apple	18
Apple juice	25
Pineapple	9
Pear	11

With the search word in E2, enter the following formula. It assumes descriptions are in A2:A6, sales are in B2:B6, and E2 contains apple:

=SUMIF(A2:A6,"*"&E2&"*",B2:B6)

The result is 64: Excel adds 12 + 18 + 25 + 9. “Pineapple” is included because it contains the letters “apple”; “Pear” is not. This is useful when descriptions contain extra words, but it also illustrates why a short search term can match more rows than expected. If you do not want “Pineapple,” use a more specific phrase or structure the data so the product category has its own column.

Choose the right wildcard pattern

The position of the asterisk determines where Excel can find the search text. These patterns cover common variations:

Anywhere in the cell: *apple* finds the text at the start, middle, or end.
At the beginning: apple* matches cells that start with “apple,” such as “apple juice.”
At the end: *apple matches cells that end with “apple,” such as “green apple.”
One unknown character: appl? uses the question mark to match exactly one character in that position, so it can match “apply” or “apple.”

For example, to find descriptions that start with the value in E2, use the following formula. It assumes descriptions are in A2:A100, amounts are in B2:B100, and the prefix is in E2:

=SUMIF(A2:A100,E2&"*",B2:B100)

To require an exact text match instead, omit the wildcards. With the same cell layout and the complete text to match in E2, use =SUMIF(A2:A100,E2,B2:B100). This matches cells equal to the criterion rather than cells that merely contain it. SUMIF text matching is not case-sensitive, so “Apple” and “apple” are treated alike.

Use a cell reference for a changeable search

Putting the search phrase in a separate cell makes a report easier to reuse. Change E2 from “apple” to “juice,” for instance, and the formula recalculates without editing the formula itself. This is convenient for a summary sheet, a user-selected keyword, or a list of product descriptions that changes over time.

When copying a formula down a column, consider locking the data ranges with dollar signs while leaving the search-cell reference relative. With descriptions in A2:A100, amounts in B2:B100, and the first search term in E2, this version keeps the source ranges fixed if copied down:

=SUMIF($A$2:$A$100,"*"&E2&"*",$B$2:$B$100)

As the formula moves to the next row, E2 becomes E3, while both ranges remain unchanged. If you instead copy the formula sideways and want the search cell to stay in column E, use $E2 as the reference. Dollar signs control which row or column changes when a formula is copied; they do not change the matching behavior.

Sum rows that meet more than one condition

Use SUMIFS when a row must satisfy multiple conditions, such as a description containing a keyword and a region equaling “West.” SUMIFS takes the sum range first, followed by pairs of criteria ranges and criteria. For descriptions in A2:A100, regions in C2:C100, amounts in B2:B100, and a keyword in E2, use:

=SUMIFS(B2:B100,A2:A100,"*"&E2&"*",C2:C100,"West")

This adds amounts only for rows where the description contains E2 and the region is West. The criteria ranges need to cover corresponding rows. SUMIFS is also suitable for more than two conditions: add another criteria-range and criterion pair for each additional rule. If you only need one condition, SUMIF is the more direct choice.

Case-sensitive matching and other alternatives

Because SUMIF ignores letter case, it cannot distinguish “ABC” from “abc.” If that distinction matters, a case-sensitive formula can use FIND inside SUMPRODUCT. For descriptions in A2:A6, amounts in B2:B6, and a case-sensitive search value in E2, use:

=SUMPRODUCT(--ISNUMBER(FIND(E2,A2:A6)),B2:B6)

If E2 contains Apple, FIND recognizes that exact capitalization but not “apple.” ISNUMBER identifies the successful searches, and the double unary converts the TRUE/FALSE results into 1s and 0s for SUMPRODUCT to use. In the worked example, this returns 25 for “Apple juice,” assuming the other matching entries use lowercase “apple.” If E2 contains lowercase apple, the result is 39 from “Red apple,” “Green apple,” and “Pineapple.” This approach is more complex than SUMIF and is unnecessary unless case sensitivity is a real requirement.

For simpler summaries, a helper column can label rows that contain the keyword, after which a regular sum can use that label. A helper column is easier to inspect when users need to see which records qualify, while a single SUMIF formula keeps the worksheet more compact. Choose based on whether transparency or a concise summary is more important.

Common problems and how to fix them

The total is zero: Check that the keyword is in the expected cell, the ranges cover the right rows, and the sum range contains numeric values. Also check for extra spaces or a criterion that is too specific.
The total is larger than expected: A short search word may occur inside unrelated words, as “apple” does in “pineapple.” Try a longer phrase or a dedicated category field.
The result includes rows that should not match: Remember that *text* means the text can appear anywhere and may be part of another word. SUMIF does not interpret spaces as word boundaries.
A blank search cell returns an unexpectedly broad total: In a formula that joins a blank cell to two asterisks, the criterion becomes **. Avoid leaving the search input blank or add a separate check before using the result.
Some expected amounts are missing: Confirm that amounts are stored as numbers, not text that looks like a number. Also verify that every description and its amount occupy corresponding rows.

If a search term itself contains an asterisk or question mark, Excel may treat it as a wildcard rather than as a literal character. A tilde (~) is the escape character for wildcard symbols in criteria. For example, the pattern ~* searches for a literal asterisk. This is an uncommon case, but it matters when matching product codes or text that includes wildcard characters.

Frequently Asked Questions

Does SUMIF find text anywhere in a cell?
Yes. Put an asterisk before and after the search text in the criterion, as in *text*. That allows any characters before or after the text. Without the asterisks, a text criterion generally requires an exact match.

Is SUMIF case-sensitive?
No. SUMIF’s text criteria are case-insensitive, so uppercase and lowercase versions match equally. Use a FIND-based approach such as SUMPRODUCT if capitalization must be distinguished.

Can SUMIF match part of a word?
Yes. A contains-text criterion can match a sequence inside a larger word, not only a separate word. The “apple” criterion also matches “pineapple,” so use a more distinctive phrase or separate fields when that distinction matters.

Can the text to find come from another cell?
Yes. Join the wildcard characters to a cell reference, such as "*"&E2&"*". Changing E2 then changes the search phrase without requiring a formula edit.

Should I use SUMIF or SUMIFS?
Use SUMIF for one criterion, such as a description containing a keyword. Use SUMIFS when the same row must meet multiple criteria, such as containing the keyword and belonging to a particular region.

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.