SUMIF Contains Text: Sum by Partial Match

SUMIF contains text adds the numbers for every row where a word appears anywhere in a cell, not only where the cell matches it exactly. You do it with the asterisk wildcard: =SUMIF(A2:A8,"*hotel*",B2:B8). This is the go-to formula for messy descriptions, such as expense lines or product names, where the word you care about sits inside longer text.

Why an exact SUMIF returns 0 for partial text#

A plain text criteria must match the whole cell. Here is a short expense list in A1:B8:

=SUMIF(A2:A8,"hotel",B2:B8) returns 0, because no cell is exactly "hotel". Both hotel lines have a city after the word. To match part of the text, you need a wildcard. The basics of SUMIF are in how to use SUMIF.

How to sum if a cell contains text#

Put an asterisk (*) on each side of the word. In a criteria, * means "any number of characters, including none":

=SUMIF(A2:A8,"*hotel*",B2:B8)

The result is 600: 320 for Hotel London plus 280 for Hotel Paris. SUMIF checks column A for the pattern and adds the matching amounts from column B. It ignores capitals, so "hotel" matches "Hotel".

Here are more totals from the same list:

"Contains airport" adds Taxi to airport (45) and Airport parking (36), because the word can sit at the start or the end. The <> in front of a pattern means "not", so the last row adds everything except the two hotels: 863 - 600 = 263.

There is also the ? wildcard, which stands for exactly one character. The guide to wildcards in COUNTIF and SUMIF covers both.

How to use a word from a cell#

To change the word without editing the formula, type it in a cell and join the asterisks to it with &. With taxi in D1:

=SUMIF(A2:A8,"*"&D1&"*",B2:B8)

The result is 73 (45 + 28). Excel builds the text "*taxi*" and then adds. Type hotel in D1 instead and the total becomes 600.

Keep the asterisks inside quotes and the cell outside them. Writing "*D1*" looks for the letters D1 and returns 0. This setup is ideal for a small summary: list your keywords down column D, put the formula next to the first with the ranges locked ($A$2:$A$8 and $B$2:$B$8), and fill down (Windows: Ctrl+D; Mac: ⌘D).

How to sum if a cell contains two words#

When a row must contain both words, use SUMIFS with the text range twice:

=SUMIFS(B2:B8,A2:A8,"*taxi*",A2:A8,"*airport*")

The result is 45: only "Taxi to airport" contains both words. SUMIFS puts the sum range first and adds a row only when every condition is TRUE. The comparison of SUMIF vs SUMIFS explains the argument order.

For either word (OR logic), add two SUMIFs, but watch for overlap. =SUMIF(A2:A8,"*taxi*",B2:B8)+SUMIF(A2:A8,"*airport*",B2:B8) gives 73 + 81 = 154, yet the real spend on taxi or airport lines is only 109 (45 + 28 + 36). "Taxi to airport" was added twice, once for each word. Adding two SUMIFs only works when no row contains both words.

Case-sensitive totals with FIND#

SUMIF never cares about capitals. If case matters, for example a code "PRO" that must not match "pro", use SUMPRODUCT with FIND, which respects capitals:

=SUMPRODUCT(ISNUMBER(FIND("Hotel",A2:A8))*B2:B8)

FIND returns a position when it finds "Hotel" and an error when it doesn't. ISNUMBER turns that into TRUE or FALSE, and multiplying by the amounts keeps only the matching rows. Here the result is still 600, because both hotel lines use a capital H.

Why SUMIF contains text returns the wrong total#

If the total is 0 or looks off, check these:

1. No asterisks. "hotel" only matches cells that are exactly "hotel". Use "*hotel*".
2. The cell reference is inside the quotes. Write "*"&D1&"*".
3. A word inside another word. "*cat*" also matches "Catering" and "Location". Add spaces to the pattern, like "* cat *", or check the matches with a COUNTIF first.
4. Misaligned ranges. The text range and the sum range should start on the same row and be the same size.
5. Amounts stored as text. SUMIF skips numbers stored as text. Convert them with VALUE.

To count the matching rows instead of adding them, use COUNTIF contains text. To leave out one exact value rather than a pattern, see SUMIF not equal to, and for totals above a number, read SUMIF greater than.

How to build a spending summary by keyword#

Keyword totals are most useful side by side. List your keywords in D2:D4 (taxi, hotel, train), type this in E2 and fill down:

=SUMIF($A$2:$A$8,"*"&D2&"*",$B$2:$B$8)

The three totals add up to 737, while the whole Amount column is 863. The gap of 126 is Airport parking (36) and Team lunch (90): the lines no keyword matched. Checking the gap like this shows which rows your keywords miss, and it also warns you when a row matched two keywords and was added twice.

Practise it#

Wildcards turn a strict SUMIF into a flexible one, and they are a favourite in data-cleaning test tasks. Practise wildcards in criteria in a live spreadsheet, then practise SUMIF to lock in the argument order. Each lesson checks your formula instantly. The SUMIF function guide is there when you need the syntax.