Text in SUM range
SUM returns #VALUE! when a cell in the range contains a label like "TBD" instead of a number.
What does #VALUE! mean in Google Sheets?
#VALUE! means Google Sheets cannot interpret an argument as the type the function expects. Common causes include text in a math formula, invalid dates, wrong delimiter locale, or passing a range where a scalar is required.
When to use it
Learn #VALUE! when importing CSV data, merging text and numbers, or debugging DATE and arithmetic formulas on messy real-world sheets.
When to skip it
Do not assume every #VALUE! needs VALUE() or regex cleanup. Sometimes the fix is fixing the source data entry rules with validation.
How it works
-
1
Click the cell and inspect each argument in the formula bar for text mixed into numeric columns.
-
2
Use ISNUMBER, ISTEXT, and ISDATE on suspect cells to see what Sheets thinks the type is.
-
3
Replace locale wrong separators if formulas use commas where your sheet expects semicolons.
-
4
Wrap text numbers with VALUE() or multiply by 1 only after confirming the text is numeric.
-
5
Check DATE and DATEVALUE inputs for impossible month or day values.
-
6
Split complex formulas with LET or helper columns to find which argument fails.
Examples in Google Sheets
FIND on empty cell
Some text functions return #VALUE! when the search string is missing in certain combinations.
Wrong date format
DATEVALUE on "32/13/2024" or ambiguous US vs EU dates can yield #VALUE!.
Build this without starting from a blank cell
Use a Better Sheets tool for #VALUE! error, then watch a walkthrough when you want the full pattern.
Better Sheets resources
Common mistakes
- Blindly wrapping everything in VALUE() instead of fixing data entry at the source.
- Pasting CSV with currency symbols and expecting SUM to work without cleaning.
- Using FIND with start position 0 or negative values.
- Concatenating dates as text then feeding them to DATE without parsing.
- Copying formulas from a comma-locale sheet into a semicolon-locale file without editing.
Frequently asked questions
- Why does VLOOKUP return #VALUE! instead of #N/A?
- Often the col_index_num is invalid or a argument type is wrong, not a missing key.
- Can spaces cause #VALUE!?
- Spaces alone usually do not. Non-numeric text in a math operation does.
- How do I find which argument fails?
- Test each part in its own cell or use LET to name intermediate results.
- Does QUERY return #VALUE!?
- Yes on parse errors or type mismatches in the query string or source data.
- Is #VALUE! the same as #NUM!?
- No. #NUM! is usually invalid numeric operation like SQRT of negative. #VALUE! is wrong type.
- Can ARRAYFORMULA cause #VALUE!?
- Yes when inner functions receive incompatible array sizes or types.
- How do I prevent #VALUE! on imports?
- Clean columns on a staging tab before formulas reference them.
- Should I use IFERROR for #VALUE!?
- Only after you understand the cause, or you may hide fixable data problems.
Related Tutorials
Watch how #VALUE! error works
Most Common Errors and How To Handle Them in Google Sheets
How to Merge Cells in Google Sheets
Related blog posts
Guides that explain #VALUE! error in more depth.
Most Common Errors and How To Handle Them in Google Sheets
Most Common Errors and How To Handle Them in Google Sheets #N/A error #REF! error #NAME? error #DIV/0! Error #VALUE! Error #NUM! error #NULL! error
Read post →Single Biggest Mistake When Converting Excel to Google Sheets
You might think the biggest mistakes when converting Excel spreadsheets to Google Sheets is how they work internally. But it's not. It's actually that you have to convert them. Google Drive has a unique function in that it does not actually convert your Excel documents unless you want to.
Read post →Google Sheets Error: Unknown Range Name and How to Fix it
A user asked me about an error, which is the "Unknown Range Name." By the time you’ve read this tutorial, you’ll know how to fix this error. What is the Unknown Range Name error? It’s an error that you get when the value that was entered does not have the correct format. Another cause of this error is when the value that was entered is outside the expected range. As you can see right here, there’s the "Unknown Range Name" error. Now, what that means is that there is supposed to be ranges ...
Read post →Related glossary terms
Google Sheets formula syntax
Every formula starts with = followed by a function name and arguments in parentheses. Ranges use A1 notation, text sits in quotes, and operators like + and & combine values. Sheets recalculates when inputs change, so syntax errors show as #NAME? or #ERROR! in the cell.
Read guide →Locale separators
Locale controls whether function arguments separate with commas or semicolons and whether decimals use dot or comma. A sheet set to France may expect =SUM(A1;A10) while US expects =SUM(A1,A10). CSV imports and array literals also clash when decimal commas meet comma separators. File > Settings > Locale sets the default behavior for new formulas in that file.
Read guide →Data validation
Data validation restricts what can be entered in a cell before bad data spreads through your model. Rules can enforce dropdown lists, number ranges, dates, text length, or custom formulas that return TRUE. Invalid entries can be rejected or warned with a note. Validation is the front door quality check for shared trackers and intake sheets.
Read guide →normalizing data in Sheets
Normalizing means making inconsistent inputs follow one reliable shape: same date format, trimmed text, unique keys, one fact per row, and separate columns for categories that were crammed into one field. Clean data makes VLOOKUP, QUERY, and dashboards dependable.
Read guide →Done reading about #VALUE! error?
Membership unlocks 636+ tutorials, unlimited generators, and every template. Practical lessons. Zero fluff.
Need this once
Jump to a free tool or a single tutorial for this topic.
Learning Sheets for real
Unlock the full library, generators, and templates with membership.