SUMIF Not Working With Text? Fix Common Criteria Issues

Yes—Excel’s SUMIF can use text as its criterion, such as a department name or product label, while adding the corresponding numeric values. If it returns zero or the wrong total, check that the criterion matches the source text, the sum range contains numbers, and the ranges line up.

How SUMIF works with text

SUMIF checks one range against a criterion and adds values from a corresponding sum range for rows that meet it. Text is valid in the criterion; it is the values being added that generally need to be numeric. The function is not limited to comparing numbers.

For example, suppose column A contains regions and column B contains sales: A2 is North with 120 in B2, A3 is South with 80 in B3, A4 is North with 45 in B4, and A5 is West with 60 in B5. With regions in A2:A5 and sales in B2:B5, this formula returns 165 because the two North rows contribute 120 and 45:

=SUMIF(A2:A5,"North",B2:B5)

The text criterion can be typed into the formula or read from a cell. The comparison is not case-sensitive, so “North” and “NORTH” match the same text values. A criterion without wildcard characters normally asks for a match to that text, rather than a partial-word search.

Check the SUMIF arguments and range sizes

The syntax is SUMIF(range, criteria, [sum_range]). The first argument is where Excel looks for matches. The second is the condition, such as a word or a reference to a cell containing a word. The optional third argument identifies what to add. If you leave it out, Excel adds matching cells in the first range itself.

That last detail causes a common mistake: using a text range as the first argument and forgetting the numeric sum range. In that case Excel looks for the text correctly, but attempts to sum the matching cells in the text range, which usually produces zero. With department names in A2:A20 and amounts in B2:B20, include both ranges:

=SUMIF(A2:A20,"Support",B2:B20)

Before troubleshooting the words themselves, verify that the first and third ranges cover corresponding rows. For example, A2:A20 paired with B2:B19 omits or misaligns a row. Excel permits ranges of different sizes in some circumstances, but matching dimensions make the intended row-by-row relationship clear and avoid surprising results.

Use a cell reference for the text criterion

A criterion can be a cell reference, which is useful when a report needs to change according to a selected department. If D2 contains the text Support, with department names in A2:A20 and amounts in B2:B20, this formula returns the total for rows whose department matches D2:

=SUMIF(A2:A20,D2,B2:B20)

Do not put quotation marks around a cell reference. Quotation marks make a criterion such as "D2" literal text, so Excel looks for the characters D2 instead of reading the value in cell D2.

When a criterion combines an operator with a cell value, join them with the ampersand. For instance, if D2 contains a minimum amount, with amounts in B2:B20 and labels in A2:A20, the following sums labels for rows whose amount is greater than D2:

=SUMIF(B2:B20,">"&D2,A2:A20)

However, that example would add the labels, not amounts, because the third argument is A2:A20. To total the amounts greater than D2, use B2:B20 as the sum range as well. This illustrates why it helps to read the arguments in order: a correct condition does not guarantee that the formula is adding the intended column.

Common reasons a text criterion returns zero

When a formula looks right but returns 0, investigate the source data before rebuilding the formula. Often the visible text is not identical to the underlying cell value, or the rows that match do not contain usable numbers to add.

Extra spaces: A value such as “North ” has a trailing space and may not match the criterion “North”. Leading spaces can cause the same problem. Spaces are difficult to see in a worksheet.
Different characters: A hyphen, apostrophe, accent, or punctuation mark may differ even when two labels look nearly alike. A standard hyphen and a longer dash, for example, are distinct characters.
Wrong criterion reference: The referenced cell may be blank, contain a different label, or include a space. Check the actual contents of the criterion cell.
Wrong sum range: The formula may match text correctly but point to a blank column, the wrong values, or cells containing text-formatted numbers.
Numbers stored as text: A value that looks like 120 can be stored as text. SUMIF may then fail to add it as an ordinary numeric value. Formatting a cell as Number alone does not always convert existing text into a number.
Misaligned rows: The category and amount ranges may start on different rows, causing Excel to associate a label with the wrong amount.

To see whether the criterion matches anything at all, use COUNTIF as a separate diagnostic. With regions in A2:A20 and the intended label in D2, this returns the number of matching cells; if it returns zero, focus first on the criterion and source text rather than the sum range:

=COUNTIF(A2:A20,D2)

Clean inconsistent text safely

If imported data has extra ordinary spaces or nonbreaking spaces, a helper column can make the labels consistent. TRIM removes extra ordinary spaces at the beginning and end and reduces repeated ordinary spaces between words. Imported web data may contain a nonbreaking space, which TRIM alone does not remove. With the original label in A2, this helper formula replaces nonbreaking spaces with ordinary spaces and then trims the result:

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

For a cell containing North followed by a nonbreaking space, the result is North without that extra character. Fill the formula down, then use the cleaned helper column as the criteria range in SUMIF. Keep the original data until you have checked the cleaned values; replacing unusual characters can change labels if those characters were meaningful.

For text numbers, convert the source values to real numbers before relying on a total. Depending on the data, Excel’s warning icon may offer Convert to Number, or you can use Data > Text to Columns and complete the conversion. Another option is a helper formula using VALUE, but first check for currency symbols, separators, or other characters that may prevent conversion. After conversion, confirm that a known sample cell behaves as a number, for example by checking whether it right-aligns under the current format or by testing a small total.

Use wildcards for partial text matches

If the category contains extra descriptive words, a full exact label may not be the right criterion. Excel supports two useful wildcard characters in SUMIF criteria: an asterisk (*) represents any sequence of characters, while a question mark (?) represents one character. A tilde (~) lets you search for a wildcard character literally.

With product descriptions in A2:A6 and values in B2:B6, suppose the matching descriptions are Apple juice for 10, green apple for 20, and Pineapple for 5. The following formula returns 35 because each description contains the word sequence apple:

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

To match descriptions that start with Apple, use "Apple*" as the criterion. To match a literal asterisk in the source text, escape it with a tilde, as in "~*". Wildcards are useful for inconsistent descriptions, but they can also include unintended rows: “*apple*” matches Pineapple as well as Apple. Choose the pattern based on the actual data and inspect the rows it is likely to include.

When SUMIFS or another approach is a better fit

SUMIF handles one condition. If you need to add amounts for a text label and another condition, such as a region and a date, use SUMIFS, which supports multiple criteria. Its argument order differs: the sum range comes first, followed by pairs of criteria ranges and criteria. This distinction matters when adapting a SUMIF formula; copying its argument order into SUMIFS can produce an incorrect result.

For a single text condition, SUMIF is generally the simpler choice. If the text must match case exactly, note that SUMIF is case-insensitive; it does not distinguish “north” from “North”. A case-sensitive requirement needs a different formula design. Also consider a PivotTable when the goal is to summarize many labels and review totals by category, rather than calculate one condition in a cell.

A quick troubleshooting sequence

Work through these checks in order so you can identify whether the problem is the criterion, the data, or the sum range:

Confirm the first range contains the labels you want to test and the third argument points to the numeric values you intend to add.
Check that the ranges start and end on matching rows, and that the formula includes all relevant data rows.
Test the criterion with COUNTIF. A zero count suggests a text mismatch or the wrong range; a positive count means matches exist, so inspect the associated sum cells.
Compare a source label and the criterion cell for leading spaces, trailing spaces, punctuation, and hidden characters. Try a wildcard only if partial matching is intended.
Check a matching sum cell for a number stored as text, blanks, or an unexpected value. Convert imported data where necessary, then recalculate and verify the total against a few rows by hand.

In Excel for Microsoft 365 on Windows, formulas normally recalculate automatically. If results appear stale, check Formulas > Calculation Options > Automatic. Excel for Mac has the same SUMIF function and formula logic; ribbon placement can differ slightly by release. In some regional settings, Excel uses semicolons instead of commas between formula arguments. If Excel rejects a formula copied from elsewhere, the local list separator may be the reason.

Frequently Asked Questions

Can SUMIF sum text values?
SUMIF can use text as its criterion, but its purpose is to add values. Text in the sum range is not added as a numeric amount. Convert number-like text to numbers if those values should contribute to the total.

Does SUMIF distinguish uppercase and lowercase?
No. SUMIF text matching is case-insensitive, so changes in capitalization alone do not create separate matches. Use a case-sensitive formula approach if capitalization must determine which rows qualify.

Why does SUMIF return zero when the word is visibly present?
The stored value may include a space or hidden character, the criterion may point to the wrong cell, or the sum range may contain text rather than numbers. Test matches with COUNTIF, then check the corresponding sum cells.

How do I sum cells that contain a word anywhere in the text?
Use an asterisk on both sides of the word in the criterion, such as "*apple*". This matches the word sequence anywhere in a description, so it may include longer words such as Pineapple.

Does SUMIF work with a criterion stored in another cell?
Yes. Put the desired label in a cell and use that cell reference as the criteria argument without quotation marks. If the criterion combines a comparison operator with a cell value, join them with an ampersand.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.