Excel SUMIF Quotation Marks Not Working? Fix the Criteria

If Excel SUMIF quotation marks are not working, first check that text criteria use straight double quotation marks and that any comparison operator is joined to a cell reference with an ampersand. For example, use =SUMIF(A2:A6,"Apples",B2:B6) to sum amounts for rows labeled Apples; if the formula still returns zero or an error, check the criteria text, cell values, and range alignment.

What quotation marks do in SUMIF

SUMIF adds values when a corresponding cell meets one condition. Its arguments are the range to inspect, the criterion to apply, and, optionally, the range to add. Quotation marks belong around criteria that Excel should interpret as text or as a comparison expression. They are part of the formula’s syntax, not characters that Excel looks for in the worksheet cell.

For example, if a cell contains the word Apples, the criterion is written as "Apples". Excel uses the quotes to recognize the text criterion, then compares the cell’s contents with Apples. The worksheet cell should normally contain Apples without quotation marks. If it literally contains quote characters, the criterion must account for those characters too.

A common misconception is that every SUMIF criterion needs quotation marks. A direct number, such as 10, can be used without them. But text needs quotes, and comparison operators such as greater than or less than must be included in a text criterion. When the value comes from a cell, join the operator and cell reference rather than typing the reference inside the quoted text.

Check the basic SUMIF formula first

Start with a small, clear example. Assume A2:A6 contains Apples, Pears, Apples, Oranges, and Apples, while B2:B6 contains 8, 4, 6, 9, and 5. The text criterion Apples should match three rows and add 8, 6, and 5.

With category names in A2:A6 and amounts in B2:B6, enter =SUMIF(A2:A6,"Apples",B2:B6). The result is 19. The first range contains the labels Excel checks, the quoted word is the criterion, and the final range contains the numbers to add.

In Excel for Microsoft 365 on Windows, type the formula in a blank cell and press Enter. If you prefer to build it with the Function Arguments dialog, select the cell, choose Formulas > Math & Trig > SUMIF, and fill in the range, criteria, and sum range. On a Mac, the function and formula work the same way; ribbon labels and dialog placement can differ slightly. Excel for the web also supports SUMIF, though its ribbon layout is different.

Make sure the formula uses straight quotation marks, the plain vertical character commonly found on a keyboard. Curly quotation marks copied from formatted text may not be accepted as formula delimiters. If you are unsure, delete the quote characters and type them again directly in Excel.

Use operators and cell references correctly

Comparison criteria are another frequent source of confusion. A criterion such as greater than or equal to 10 is written as text because it combines an operator and a value. In a worksheet, a criterion like ">=10" is enclosed in quotes. If the threshold is stored in D2, do not write ">=D2": that asks Excel to compare against the literal characters “D2,” not the value in the cell.

Assume B2:B6 contains 8, 4, 6, 9, and 15, and D2 contains the number 10. With the values in B2:B6 and the threshold in D2, enter =SUMIF(B2:B6,">="&D2). The result is 15, because only 15 is at least 10. The ampersand joins the operator text to the value stored in D2.

The same principle works when the condition is a text label. Assume A2:A6 contains the category names from the earlier example, B2:B6 contains the amounts, and D2 contains Apples. With labels in A2:A6, amounts in B2:B6, and the criterion in D2, enter =SUMIF(A2:A6,"="&D2,B2:B6). The result is 19. The equals sign is optional for a straightforward text match, but including it makes the comparison explicit.

In formulas, the ampersand must be outside the quoted text and between the operator and the cell reference. The form ">="&D2 combines the operator with D2’s current value. If D2 changes from 10 to 12, the criterion updates automatically.

Distinguish text, numbers, and dates

A formula can be syntactically correct and still find no matching values. Check whether the criterion and worksheet data represent the same kind of value. A cell that looks like 10 might contain the number 10, the text string “10,” or a value with unseen spaces. Imported data is especially likely to contain numbers stored as text or labels with extra spaces.

Look at the source cells as well as the formula. Select a suspicious cell and inspect the formula bar; check whether numbers are aligned or flagged as text, and use Excel’s warning options if they appear. If a whole column needs conversion, select it and use Data > Text to Columns, then follow the wizard to finish. Review a sample of the converted values before relying on the total. Avoid changing data types blindly when leading zeros, identifiers, or codes must remain text.

Dates are stored by Excel as serial numbers, even though cells usually display them in a date format. For date conditions, make sure the cells contain real Excel dates rather than text that only looks like a date. When a comparison uses a date in another cell, join the operator to that cell reference with an ampersand, just as with a numeric threshold. Regional date formats can make imported text particularly difficult to interpret, so confirm that Excel recognizes the values as dates.

Also consider spaces and spelling. “Apples” and “Apples ” may look alike on screen but are not identical text. SUMIF’s ordinary text matching is not case-sensitive, so capitalization alone usually does not explain a missing match. Extra spaces, different punctuation, or a singular versus plural label can.

Work through common errors in order

If SUMIF gives zero, an incorrect total, or a formula error, use a short troubleshooting sequence instead of changing several parts at once:

Confirm that the formula begins with an equals sign and that every opening quotation mark has a matching closing quotation mark.
Retype any suspicious quotes as straight double quotes. Curly quotes copied from a document are not reliable formula delimiters.
Check that the criterion matches the actual cell contents, including spaces and punctuation. Remember that quotes in the formula do not mean the worksheet value should be surrounded by quotes.
For an operator with a cell reference, check that the formula uses a pattern such as ">"&D2, not ">D2".
Verify that the criteria range and sum range cover corresponding rows. If the criteria range is A2:A20, the sum range should normally cover the same rows, such as B2:B20.
Check that the sum range contains numeric values and that the data has not shifted because of inserted rows, filtered exports, or an incorrectly selected range.
If Excel displays the formula itself instead of a result, check whether the cell is formatted as Text or whether Show Formulas is enabled. Change the cell to General if needed, then re-enter the formula.

Excel’s default argument separator is a comma in many installations, including the standard examples here. Some regional settings use a semicolon instead because the system’s list separator differs. If Excel rejects a correctly written comma-separated formula, check the separator expected by that installation rather than replacing quotation marks at random. The formulas shown here use commas as required by the usual Microsoft 365 for Windows setup.

Use wildcards or match quote characters literally

Quotation marks also surround wildcard criteria. An asterisk matches any sequence of characters, while a question mark matches one character. These are useful when labels share a beginning or contain a predictable pattern. For example, North* can match North, Northside, or North Shop. The asterisk is interpreted as a wildcard, not as a character that must appear in the cell.

Assume A2:A5 contains North, Northside, South, and North Shop, while B2:B5 contains 12, 7, 9, and 4. With labels in A2:A5 and amounts in B2:B5, enter =SUMIF(A2:A5,"North*",B2:B5). The result is 23, from 12 + 7 + 4.

If a criterion needs to match a literal asterisk or question mark rather than use it as a wildcard, place a tilde before that character in the criterion. A tilde itself can be matched by doubling it. This distinction matters when product names or codes contain wildcard characters.

To include a literal double-quote character inside a text criterion, double the quote within the formula’s quoted text. Assume A2:A3 contains He said “yes” with ordinary quote characters around yes in the cell, and B2:B3 contains 5 and 7. With those descriptions in A2:A3 and amounts in B2:B3, enter =SUMIF(A2:A3,"He said ""yes""",B2:B3). The result is 5 if only A2 has that exact text. This is a different situation from the normal formula delimiters: the doubled pair represents a quote character to match in the cell.

When SUMIF is not the right fix

SUMIF handles one condition. If the desired total depends on multiple conditions, use SUMIFS, which sums a range when all supplied criteria are met. For instance, a report might need sales for one product in one region. In that case, checking only the product with SUMIF would include every region, so the issue is not quotation marks but the number of conditions required.

SUMIF and SUMIFS use different argument orders. SUMIF places the criteria range first and the sum range last. SUMIFS places the sum range first, followed by pairs of criteria ranges and criteria. When switching functions, read the argument order carefully; formulas can be valid yet return an unexpected total if the ranges are assigned to the wrong roles.

For one condition, SUMIF is usually the clearest choice. For multiple independent conditions, SUMIFS is generally easier to maintain than multiplying separate tests into a complicated formula. Neither function fixes inconsistent source data, so standardizing labels and confirming numeric values still matters.

Frequently Asked Questions

Do I always need quotation marks around a SUMIF criterion?
Text criteria and criteria that combine operators with values are written in quotation marks. A direct numeric criterion can be entered as a number without quotes. If a criterion refers to a cell, use a cell reference and join it to any operator with an ampersand.

Why does SUMIF return zero when the text looks identical?
Check for leading or trailing spaces, different punctuation, a misspelling, or a criterion that points to the wrong cell. Ordinary SUMIF matching is not case-sensitive, so a capitalization difference alone is unlikely to be the cause.

Can I put a cell reference inside the quotation marks?
No, not if you want Excel to use the cell’s value. Quoted text such as “>D2” treats D2 as characters. Use a construction such as “>”&D2 to join the operator to the value in D2.

Why does Excel show the formula instead of calculating it?
The formula cell may be formatted as Text, or Show Formulas may be switched on. Set the cell format to General and re-enter the formula if needed, and check the Formulas tab for the Show Formulas setting.

Are SUMIF quotation marks different in Excel for Mac or the web?
The formula’s quotation-mark rules are the same in Excel for Microsoft 365 on Windows, Mac, and the web. Ribbon layouts and regional argument separators can differ, but straight double quotes and correctly joined cell references work the same way.

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.