#NAME? Error in SUMIF: Causes and Solutions

#NAME?

+SUMIF

# Fixing the #NAME? Error in SUMIF The #NAME? error in SUMIF is one of the most common formula mistakes you'll encounter, but it's also one of the easiest to fix. This error appears when Excel doesn't recognize the formula text you've entered, typically due to a typo or syntax issue in your SUMIF statement. SUMIF is prone to #NAME? errors because even small misspellings—like "SUMIF" instead of "SUMIF"—will trigger this response. Other culprits include incorrect range references, missing commas between arguments, or accidentally using special characters that Excel can't parse. The good news: you're not alone. Thousands of Excel users face this daily, and the solution usually takes seconds. By understanding the common causes and checking your formula structure against the correct syntax =SUMIF(range, criteria, [sum_range]), you'll quickly identify and resolve the problem.

Why SUMIF causes #NAME?

Misspelled or unquoted criteria

The criteria argument is misspelled, missing quotes around text values, or references an undefined named range. Excel cannot recognize the criteria syntax, triggering #NAME?.

=SUMIF(A2:A10, Apples, B2:B10) — missing quotes around 'Apples'; correct: =SUMIF(A2:A10, "Apples", B2:B10)

Invalid range reference syntax

The range argument uses incorrect syntax, such as a missing colon in a range, an invalid sheet reference, or a typo in the range name. Excel interprets this as an undefined name.

=SUMIF(A2-A10, "Apples", B2:B10) — dash instead of colon; correct: =SUMIF(A2:A10, "Apples", B2:B10)

Undefined named range in criteria or range

The formula references a named range that doesn't exist or was deleted. Excel cannot resolve the name, resulting in #NAME?.

=SUMIF(SalesData, "Apples", SumRange) where 'SalesData' or 'SumRange' named ranges are not defined

Step-by-Step Solution

1Click on the cell displaying the #NAME? error to select it, then look at the formula bar (Ctrl+` to toggle formula view if needed)
2Check for typos in the SUMIF function name itself—verify it's spelled exactly as 'SUMIF' (not 'SUMIFS', 'SUMPRODUCT', or misspelled variants)
3Scan the formula for unquoted text strings in criteria—ensure any text criteria are wrapped in double quotes, like SUMIF(A:A,"text",B:B) not SUMIF(A:A,text,B:B)
4Verify that range references use proper syntax with colons (A1:A10) and not dashes or other separators; check for missing commas between the three SUMIF arguments
5If using named ranges or external references, confirm they're defined correctly—press Ctrl+Shift+F3 to check defined names, or use the Name Manager (Ctrl+F3)
6Delete the formula and retype it from scratch in the formula bar, ensuring proper parentheses closure with a final closing parenthesis
7Press Enter to execute the corrected formula and confirm the #NAME? error is resolved and calculations are accurate

Concrete Example

Sales Commission Calculation for Regional Teams

A sales manager uses SUMIF to calculate total commissions for each region based on sales amounts. The spreadsheet has columns for Region, Salesperson, and Sales Amount.

Before (error)

=SUMIF(Region,"North",SalesAmount)

After (fixed)

=SUMIF(Region,"North",Sales_Amount)

Problem: The #NAME? error appears because the formula references a range name that doesn't exist or contains a typo. The manager typed 'SalesAmount' but the actual named range is 'Sales_Amount' (with underscores instead of camelCase).

Solution: Correct the named range reference to match the exact name defined in the workbook. Use the Name Manager to verify the correct spelling, or replace it with a direct cell reference.

Prevention Tip

Verify that SUMIF is spelled correctly and that your range and criteria references don't have typos—the #NAME? error typically means Excel doesn't recognize the formula name itself, so double-check for misspellings like SUMIFF or missing equals sign at the formula start.

Free Tools to Fix Your Formulas

Use these free tools to avoid this error:

Excel Formula Generator

Describe what you want to calculate and get the Excel formula instantly

VLOOKUP Generator

Generate VLOOKUP formulas instantly by describing what you need in plain English

Excel Formula Explainer

Paste any Excel formula and get a clear, step-by-step explanation powered by AI. Understand complex formulas instantly.

Struggling with #NAME? errors in your SUMIF formulas? ElyxAI automatically detects and fixes these issues in seconds—try it free today and get back to work without the frustration.