Excel SUMIFS for Text: How to Sum Cells That Match

Formulas & functions · Updated November 22, 2026

The short answer: Excel’s SUMIFS function can sum numbers based on text criteria, but it cannot sum text values themselves. To sum numbers where a corresponding text column equals a specific word or phrase, use =SUMIFS(sum_range, criteria_range1, "text"). For partial matches, use wildcards like "*text*". If you need to add up the actual text values (like concatenating them), you must use a different approach, such as TEXTJOIN or a pivot table.

What SUMIFS Actually Does

SUMIFS is a conditional summing function. It adds up numbers in one column only when corresponding cells in other columns meet your specified conditions. The syntax is:

=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

For example, with product names in A2:A20 and sales amounts in B2:B20, to sum sales for the product “Widget”:

=SUMIFS(B2:B20, A2:A20, "Widget")

This returns the total of all B values where the A cell equals “Widget” exactly (case-insensitive).

Text Criteria: Exact Match, Case, and Wildcards

When your criteria is text, you can use three types of matches:

  • Exact match: "Widget" – matches cells containing only that text, not case-sensitive (so “widget” or “WIDGET” also match).
  • Partial match with wildcards: "*Widget*" – matches any cell containing “Widget” anywhere. The asterisk (*) represents any number of characters, and the question mark (?) represents a single character.
  • Starts with or ends with: "Widget*" or "*Widget" – use these to match text that begins or ends with a specific string.

Example: To sum sales for all products that contain “Pro” in the name (like “Pro Max” or “Pro Lite”):

=SUMIFS(B2:B20, A2:A20, "*Pro*")

Summing Text Values (Concatenation)

If you literally want to add text together (e.g., combine “Hello” and “World” into “HelloWorld”), SUMIFS won’t work because it only sums numbers. To concatenate text based on a condition, use TEXTJOIN with an IF array formula, or use a pivot table.

For example, with names in A2:A10 and categories in B2:B10, to join all names in category “VIP”:

=TEXTJOIN(", ", TRUE, IF(B2:B10="VIP", A2:A10, ""))

This is an array formula. In Excel 365, it works automatically; in older versions, you must press Ctrl+Shift+Enter. It returns a single text string like “Alice, Bob, Carol”.

Common Problems and How to Fix Them

Here are frequent issues when using SUMIFS with text:

  • No matches returned: Check for extra spaces in your data or criteria. Use =TRIM() to clean cells. Also ensure the criteria is typed exactly, including case (though case doesn’t matter, spelling does).
  • Wildcard characters not working: If you need to match a literal asterisk or question mark, prefix it with a tilde (~). For example, "*~**" matches any cell containing an asterisk.
  • Sum range has text or errors: SUMIFS ignores text and logical values in the sum range, but errors will propagate. Use =IFERROR() to handle them.
  • Criteria range is a different size: All ranges must be the same size. If they aren’t, you’ll get a #VALUE! error.

Alternatives to SUMIFS for Text

Depending on your goal, other functions may be more appropriate:

  • SUMIF – the older single-criteria version. Use =SUMIF(criteria_range, "text", sum_range) when you have only one condition.
  • SUMPRODUCT – for more complex conditions, like case-sensitive matching. Example: =SUMPRODUCT((EXACT(A2:A20,"Widget"))*B2:B20) sums only where the text is exactly “Widget” with that case.
  • FILTER (Excel 365) – to return the matching rows, then sum them: =SUM(FILTER(B2:B20, A2:A20="Widget")).
  • Pivot Table – for quick grouping and summing by text categories without formulas.
  • COUNTIFS – if you need to count text cells, not sum them.

Frequently Asked Questions

Can SUMIFS sum text values like “apple” + “banana”?

No. SUMIFS only adds numbers. To combine text, use TEXTJOIN or the & operator. For example, =A1 & B1 concatenates the contents of A1 and B1.

How do I sum cells based on text that contains a specific word?

Use wildcards: =SUMIFS(sum_range, criteria_range, "*word*"). This matches any cell containing the word anywhere. For case-sensitive matching, you’ll need SUMPRODUCT with EXACT.

Why is my SUMIFS returning 0 even though I see matching text?

Common causes: leading/trailing spaces in the criteria or data, non-printable characters, or the criteria range not being the same size as the sum range. Use =TRIM() and =CLEAN() to clean data, and verify ranges align.

Can I use SUMIFS with text that has a tilde (~) in it?

Yes, but you need to escape the tilde with another tilde. For example, to match “~5”, use "~~5" as the criteria. Otherwise, Excel treats ~ as a wildcard escape character.

Does SUMIFS work with text in the sum range if the numbers are stored as text?

No. If your sum range contains numbers stored as text, SUMIFS will ignore them. Convert them to real numbers using =VALUE() or the Text to Columns wizard, or use SUMPRODUCT with -- to coerce.

What is the difference between SUMIF and SUMIFS for text?

SUMIF has one criteria and the range order is reversed: =SUMIF(range, criteria, [sum_range]). SUMIFS supports multiple criteria and always lists the sum range first. For a single text condition, either works, but SUMIFS is more flexible for future needs.

Practical Example with Steps

Suppose you have a sales table: Column A = Product (text), Column B = Region (text), Column C = Sales (numbers). You want total sales for “Widget” in the “East” region.

  1. Enter your data in A1:C20.
  2. In a blank cell, type: =SUMIFS(C2:C20, A2:A20, "Widget", B2:B20, "East")
  3. Press Enter. The result is the sum of all C values where A equals “Widget” and B equals “East”.

If you want to include “Widget” and “Widget Pro”, use "Widget*" as the criteria.

Remember, SUMIFS is case-insensitive by default. For case-sensitive text matching, you’ll need SUMPRODUCT with the EXACT function, as shown earlier. Always double-check your ranges and criteria to avoid errors.

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.