<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: SheetError</title>
    <description>The latest articles on DEV Community by SheetError (@sheeterror).</description>
    <link>https://dev.to/sheeterror</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4211780%2Ff8bda1a8-7674-4680-8daa-c0622e0fd765.png</url>
      <title>DEV Community: SheetError</title>
      <link>https://dev.to/sheeterror</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/sheeterror"/>
    <language>en</language>
    <item>
      <title>#NAME? "Unknown function" in Google Sheets: fix the misspelled function name</title>
      <dc:creator>SheetError</dc:creator>
      <pubDate>Mon, 16 Nov 2026 03:06:14 +0000</pubDate>
      <link>https://dev.to/sheeterror/name-unknown-function-in-google-sheets-fix-the-misspelled-function-name-1971</link>
      <guid>https://dev.to/sheeterror/name-unknown-function-in-google-sheets-fix-the-misspelled-function-name-1971</guid>
      <description>&lt;p&gt;You type what looks like a normal VLOOKUP and the cell shows &lt;strong&gt;#NAME?&lt;/strong&gt;. The tooltip says &lt;em&gt;Unknown function: ‘VLOKUP’&lt;/em&gt;. The ranges are fine. The formula never ran. Sheets doesn't recognize the name.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why it happens
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;#NAME?&lt;/strong&gt; means a name in the formula isn't a function, a named range, or a named function on this spreadsheet. One missing letter is enough: &lt;code&gt;VLOKUP&lt;/code&gt; is not &lt;code&gt;VLOOKUP&lt;/code&gt;, and Sheets does not fuzzy-match function names. A bare word like &lt;code&gt;=Hello&lt;/code&gt; is also read as a name. Named functions and named ranges are local to the file, so a formula pasted from another spreadsheet can bring a name that doesn't exist here.&lt;/p&gt;

&lt;h2&gt;
  
  
  Reproduce it in a minute
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;New blank sheet. In A1 type &lt;code&gt;sku&lt;/code&gt;, B1 &lt;code&gt;name&lt;/code&gt;. A2 &lt;code&gt;101&lt;/code&gt;, B2 &lt;code&gt;Widget&lt;/code&gt;. D2 &lt;code&gt;101&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;In E2 enter:
&lt;/li&gt;
&lt;/ol&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=VLOKUP(D2,A2:B2,2,FALSE)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;E2 shows #NAME? with &lt;em&gt;Unknown function: ‘VLOKUP’&lt;/em&gt;.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;In F2 enter the same formula with the correct spelling:
&lt;/li&gt;
&lt;/ol&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=VLOOKUP(D2,A2:B2,2,FALSE)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;F2 returns &lt;code&gt;Widget&lt;/code&gt;. Leave E2 broken so the #NAME? stays visible.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;Optional second case: in G2 enter &lt;code&gt;=Hello&lt;/code&gt;. That's also #NAME?. In H2 enter &lt;code&gt;="Hello"&lt;/code&gt;. Quotes make a string, and H2 shows Hello.&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  The fix
&lt;/h2&gt;

&lt;p&gt;Correct the name. Change &lt;code&gt;VLOKUP&lt;/code&gt; to &lt;code&gt;VLOOKUP&lt;/code&gt;, or keep E2 as the broken example and use F2 as the working copy. Function names in Google Sheets are not case-sensitive (&lt;code&gt;=vlookup(...)&lt;/code&gt; is fine), but spelling is not optional. &lt;code&gt;IFNA&lt;/code&gt; is not &lt;code&gt;IFNAA&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;For a bare word, quote it (&lt;code&gt;="Hello"&lt;/code&gt;) or call a real function (&lt;code&gt;=UPPER("Hello")&lt;/code&gt;). Don't leave an unquoted identifier unless it's a defined named range.&lt;/p&gt;

&lt;p&gt;For a name you brought from another file: &lt;strong&gt;Data &amp;gt; Named functions&lt;/strong&gt; to define or import it, or &lt;strong&gt;Data &amp;gt; Named ranges&lt;/strong&gt; to create the range, or replace it with a normal range like &lt;code&gt;A2:B2&lt;/code&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Watch out for
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;It's not a Formula parse error.&lt;/strong&gt; A parse error is #ERROR! when argument separators don't match the locale (commas vs semicolons). The function name can be spelled correctly and still fail to parse. On this recreate the tooltip says &lt;em&gt;Unknown function&lt;/em&gt;, and F2 with the same commas returns Widget.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It's not #N/A.&lt;/strong&gt; #N/A means the function ran and found no match. E2 never got that far.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It's not #REF!.&lt;/strong&gt; The ranges &lt;code&gt;D2&lt;/code&gt; and &lt;code&gt;A2:B2&lt;/code&gt; still exist.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Locale won't invent a typo.&lt;/strong&gt; Sheets function names stay English (&lt;code&gt;VLOOKUP&lt;/code&gt;, &lt;code&gt;SUM&lt;/code&gt;, &lt;code&gt;IF&lt;/code&gt;) even when the UI isn't. Changing File &amp;gt; Settings &amp;gt; Locale will not turn &lt;code&gt;VLOKUP&lt;/code&gt; into a function.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Don't wrap it in IFERROR.&lt;/strong&gt; The name is wrong. Fix the name.&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://sheeterror.com/tutorials/name-error/" rel="noopener noreferrer"&gt;SheetError&lt;/a&gt;, a free library of Google Sheets error fixes (I run it).&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This article was created with the help of AI.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>googlesheets</category>
      <category>spreadsheets</category>
      <category>productivity</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>FILTER "No matches are found" #N/A in Google Sheets: two causes, read the tooltip first</title>
      <dc:creator>SheetError</dc:creator>
      <pubDate>Thu, 12 Nov 2026 01:15:01 +0000</pubDate>
      <link>https://dev.to/sheeterror/filter-no-matches-are-found-na-in-google-sheets-two-causes-read-the-tooltip-first-5elo</link>
      <guid>https://dev.to/sheeterror/filter-no-matches-are-found-na-in-google-sheets-two-causes-read-the-tooltip-first-5elo</guid>
      <description>&lt;p&gt;&lt;code&gt;=FILTER(...)&lt;/code&gt; comes back as &lt;strong&gt;#N/A&lt;/strong&gt;. The key you wanted might be missing, or it might be sitting right there. Both look like #N/A. The tooltip is what tells them apart.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why it happens
&lt;/h2&gt;

&lt;p&gt;FILTER uses #N/A for two different failures. &lt;em&gt;No matches are found in FILTER evaluation&lt;/em&gt; means the formula ran and zero rows matched the condition. &lt;em&gt;FILTER has mismatched range sizes&lt;/em&gt; means the data range and the condition are different heights, so FILTER never evaluates the condition at all. IFNA and IFERROR hide both, which is why you read the tooltip before wrapping anything.&lt;/p&gt;

&lt;h2&gt;
  
  
  Reproduce it in a minute
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;New blank sheet. In A1 type &lt;code&gt;sku&lt;/code&gt;, B1 &lt;code&gt;name&lt;/code&gt;. A2 &lt;code&gt;101&lt;/code&gt;, B2 &lt;code&gt;Widget&lt;/code&gt;. A3 &lt;code&gt;102&lt;/code&gt;, B3 &lt;code&gt;Gadget&lt;/code&gt;. A4 &lt;code&gt;103&lt;/code&gt;, B4 &lt;code&gt;Lamp&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;In D2 enter:
&lt;/li&gt;
&lt;/ol&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=FILTER(A2:B4,A2:A4=999)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;D2 shows #N/A. The tooltip is &lt;em&gt;No matches are found in FILTER evaluation&lt;/em&gt;. Sku 999 isn't in the list, so that's a real empty result.&lt;/p&gt;

&lt;ol&gt;
&lt;li&gt;In G2 enter:
&lt;/li&gt;
&lt;/ol&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=FILTER(A2:B4,A2:A5=101)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;code&gt;A2:B4&lt;/code&gt; is three rows. &lt;code&gt;A2:A5&lt;/code&gt; is four. G2 is also #N/A, but the tooltip is &lt;em&gt;FILTER has mismatched range sizes. Expected row count: 3. column count: 1. Actual row count: 4, column count: 1.&lt;/em&gt; Sku 101 is on the sheet. This isn't a miss.&lt;/p&gt;

&lt;h2&gt;
  
  
  The fix
&lt;/h2&gt;

&lt;p&gt;For the empty result, wrap with IFNA:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IFNA(FILTER(A2:B, A2:A=999), "none")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;That cell shows &lt;code&gt;none&lt;/code&gt;. Leave the original D2 broken so you can still see the #N/A.&lt;/p&gt;

&lt;p&gt;For the size error, don't wrap G2. Make the heights equal: &lt;code&gt;=FILTER(A2:B4,A2:A4=101)&lt;/code&gt; spills &lt;code&gt;101&lt;/code&gt; / &lt;code&gt;Widget&lt;/code&gt;. Fix the ranges first. Then, if a later formula has zero matches, you can add IFNA.&lt;/p&gt;

&lt;h2&gt;
  
  
  Watch out for
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;IFNA hides the size bug too.&lt;/strong&gt; &lt;code&gt;IFNA(FILTER(A2:B4,A2:A5=101),"none")&lt;/code&gt; would show &lt;code&gt;none&lt;/code&gt; even though 101 is in the list. Read the tooltip, fix mismatched heights, then wrap.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It's not a VLOOKUP type miss.&lt;/strong&gt; Number-as-text or a trailing space is a different #N/A, covered on the VLOOKUP page. FILTER is not searching with a column index.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It's not #REF!.&lt;/strong&gt; A UNIQUE overwrite, a circular loop, a VLOOKUP index past the table, or IMPORTRANGE waiting for Allow access are all #REF!, not this #N/A.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;COUNTIFS with unequal heights is #VALUE!&lt;/strong&gt;, not this #N/A. Don't copy a COUNTIFS size fix onto FILTER and expect the same error value.&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://sheeterror.com/tutorials/filter-no-match/" rel="noopener noreferrer"&gt;SheetError&lt;/a&gt;, a free library of Google Sheets error fixes (I run it).&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This article was created with the help of AI.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>googlesheets</category>
      <category>spreadsheets</category>
      <category>productivity</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>IMPORTRANGE #REF! "You need to connect these sheets" in Google Sheets: click Allow access, then read the tooltip</title>
      <dc:creator>SheetError</dc:creator>
      <pubDate>Tue, 10 Nov 2026 01:20:07 +0000</pubDate>
      <link>https://dev.to/sheeterror/importrange-ref-you-need-to-connect-these-sheets-in-google-sheets-click-allow-access-then-4kam</link>
      <guid>https://dev.to/sheeterror/importrange-ref-you-need-to-connect-these-sheets-in-google-sheets-click-allow-access-then-4kam</guid>
      <description>&lt;p&gt;You write &lt;code&gt;=IMPORTRANGE(...)&lt;/code&gt; to pull a range from another spreadsheet and the cell shows &lt;strong&gt;#REF!&lt;/strong&gt;. The URL is right, the range is right, and the tab exists. Hover the cell and the tooltip says &lt;em&gt;You need to connect these sheets&lt;/em&gt;. Nothing is broken yet. Sheets is waiting for permission.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why it happens
&lt;/h2&gt;

&lt;p&gt;IMPORTRANGE needs one-time permission for each pair of files: this destination spreadsheet reading that source spreadsheet. Until someone clicks &lt;strong&gt;Allow access&lt;/strong&gt;, Sheets won't evaluate the range at all, so even a perfectly valid &lt;code&gt;Sheet1!A1:B2&lt;/code&gt; comes back as #REF!. It isn't a missing range or a deleted cell, and the formula text is fine.&lt;/p&gt;

&lt;h2&gt;
  
  
  Reproduce it in a minute
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;Open a new spreadsheet. This is the source. Type &lt;code&gt;sku&lt;/code&gt; in A1, &lt;code&gt;name&lt;/code&gt; in B1, &lt;code&gt;101&lt;/code&gt; in A2 and &lt;code&gt;Widget&lt;/code&gt; in B2. Leave the tab named &lt;code&gt;Sheet1&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;Copy the spreadsheet id from its URL. It's the long string between &lt;code&gt;/d/&lt;/code&gt; and &lt;code&gt;/edit&lt;/code&gt; in &lt;code&gt;docs.google.com/spreadsheets/d/&amp;lt;id&amp;gt;/edit&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;Open a second blank spreadsheet. This is the destination. In A1 enter:
&lt;/li&gt;
&lt;/ol&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IMPORTRANGE("SOURCE_ID", "Sheet1!A1:B2")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;A1 shows #REF! with the &lt;em&gt;You need to connect these sheets&lt;/em&gt; prompt. (The documented first argument is the full spreadsheet URL. The id on its own also works.)&lt;/p&gt;

&lt;h2&gt;
  
  
  The fix
&lt;/h2&gt;

&lt;p&gt;Select the #REF! cell, hover it, and click &lt;strong&gt;Allow access&lt;/strong&gt;. Leave the formula as it is. The same formula spills four cells: &lt;code&gt;sku&lt;/code&gt;, &lt;code&gt;name&lt;/code&gt;, &lt;code&gt;101&lt;/code&gt;, &lt;code&gt;Widget&lt;/code&gt;.&lt;/p&gt;

&lt;p&gt;You only do this once per pair of files. Later IMPORTRANGE formulas in the same destination that point at the same source won't ask again.&lt;/p&gt;

&lt;p&gt;If it's still #REF! after the click, read the tooltip again:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Still "You need to connect these sheets"&lt;/strong&gt;: you're signed into an account that can't open the source, or the id/URL in quotes isn't the source. Open the source URL while signed in as the same user.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;A missing sheet or range&lt;/strong&gt;: that's a different #REF!, covered next.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Watch out for
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;The second #REF! has no prompt.&lt;/strong&gt; Once the files are connected, enter &lt;code&gt;=IMPORTRANGE("SOURCE_ID","Sheet2!A1:B2")&lt;/code&gt; in A4, with a tab name that doesn't exist on the source. You get #REF! again, but with no Allow access banner. The tooltip now says the sheet or range can't be found. Fix the string: match the tab name exactly, spaces included. Clicking Allow access again does nothing.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Don't wrap it in IFERROR yet.&lt;/strong&gt; &lt;code&gt;IFERROR&lt;/code&gt; hides the permission #REF! along with the prompt, so you never see the button to click. Connect first, then add error handling if you still want it.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It's not #REF! after a delete.&lt;/strong&gt; When you delete rows or columns, &lt;code&gt;#REF!&lt;/code&gt; shows up inside the formula text. With IMPORTRANGE the references are quoted strings and nothing in the formula changes.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It's not "Result too large".&lt;/strong&gt; That one happens after you're connected, usually as #ERROR!, when the pull is over the size cap. Shrink the range. A permission prompt won't help there.&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://sheeterror.com/tutorials/importrange-connect/" rel="noopener noreferrer"&gt;SheetError&lt;/a&gt;, a free library of Google Sheets error fixes (I run it). There's also a short walkthrough: &lt;a href="https://youtube.com/shorts/EgqvgZp_vjc" rel="noopener noreferrer"&gt;IMPORTRANGE #REF! - You need to connect these sheets&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This article was created with the help of AI.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>googlesheets</category>
      <category>spreadsheets</category>
      <category>productivity</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>Formula parse error in Google Sheets after pasting a formula: commas vs semicolons</title>
      <dc:creator>SheetError</dc:creator>
      <pubDate>Sat, 07 Nov 2026 03:23:16 +0000</pubDate>
      <link>https://dev.to/sheeterror/formula-parse-error-in-google-sheets-after-pasting-a-formula-commas-vs-semicolons-3jnc</link>
      <guid>https://dev.to/sheeterror/formula-parse-error-in-google-sheets-after-pasting-a-formula-commas-vs-semicolons-3jnc</guid>
      <description>&lt;p&gt;You paste a VLOOKUP from a tutorial and the cell shows &lt;strong&gt;#ERROR!&lt;/strong&gt;. The tooltip says &lt;em&gt;Formula parse error&lt;/em&gt;. The function name is spelled right. The problem is the commas.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why it happens
&lt;/h2&gt;

&lt;p&gt;The argument separator belongs to the spreadsheet's locale, not to whoever wrote the tutorial, and not to your Google account language. US and UK sheets separate arguments with commas. Locales that use a comma as the decimal mark (Germany, France, Brazil and many others) separate arguments with semicolons, because a comma can't mean both "decimal" and "next argument".&lt;/p&gt;

&lt;h2&gt;
  
  
  Reproduce it in a minute
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;New blank sheet. &lt;strong&gt;File &amp;gt; Settings &amp;gt; Locale&lt;/strong&gt;, pick Germany, then reload the spreadsheet so the locale applies.&lt;/li&gt;
&lt;li&gt;Type &lt;code&gt;101&lt;/code&gt; in A2, &lt;code&gt;Widget&lt;/code&gt; in B2 and &lt;code&gt;101&lt;/code&gt; in D2.&lt;/li&gt;
&lt;li&gt;In E2 paste:
&lt;/li&gt;
&lt;/ol&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=VLOOKUP(D2,A2:B2,2,FALSE)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;E2 shows #ERROR! with &lt;em&gt;Formula parse error&lt;/em&gt;. Try &lt;code&gt;=IF(D2=101,B2,"none")&lt;/code&gt; too. Same error, so it isn't a VLOOKUP bug. Any function with more than one argument hits it.&lt;/p&gt;

&lt;p&gt;Don't test this with &lt;code&gt;=SUM(1,2)&lt;/code&gt;. On a German locale that can read as the decimal number 1,2 and never error.&lt;/p&gt;

&lt;h2&gt;
  
  
  The fix
&lt;/h2&gt;

&lt;p&gt;Use the separator the locale expects:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=VLOOKUP(D2;A2:B2;2;FALSE)
=IF(D2=101;B2;"none")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Both return &lt;code&gt;Widget&lt;/code&gt;. If you'd rather keep commas, set &lt;strong&gt;File &amp;gt; Settings &amp;gt; Locale&lt;/strong&gt; to United States and reload.&lt;/p&gt;

&lt;p&gt;Two things not to do: changing your Google account language won't help, and wrapping the formula in IFERROR only hides a formula that never ran.&lt;/p&gt;

&lt;h2&gt;
  
  
  Watch out for
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Pasted text isn't translated.&lt;/strong&gt; A formula you paste or type as text keeps its commas, so on a semicolon locale you have to type the separators that locale expects.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Array literals are different.&lt;/strong&gt; Inside &lt;code&gt;{ }&lt;/code&gt; on a semicolon locale, a US comma between columns becomes a backslash: &lt;code&gt;{A2\B2}&lt;/code&gt; is two columns and &lt;code&gt;{A2;B2}&lt;/code&gt; stacks rows. Don't blindly swap commas for semicolons there.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It's not #NAME?.&lt;/strong&gt; That one means an unknown function name, like &lt;code&gt;VLOKUP&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;It's not QUERY's "Unable to parse query string".&lt;/strong&gt; That's a #VALUE! inside the query text itself.&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://sheeterror.com/tutorials/formula-parse-locale/" rel="noopener noreferrer"&gt;SheetError&lt;/a&gt;, a free library of Google Sheets error fixes (I run it).&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This article was created with the help of AI.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>googlesheets</category>
      <category>spreadsheets</category>
      <category>productivity</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>#DIV/0! "Function DIVIDE parameter 2 cannot be zero" in Google Sheets: blank cells count as zero</title>
      <dc:creator>SheetError</dc:creator>
      <pubDate>Wed, 04 Nov 2026 01:17:58 +0000</pubDate>
      <link>https://dev.to/sheeterror/div0-function-divide-parameter-2-cannot-be-zero-in-google-sheets-blank-cells-count-as-zero-26md</link>
      <guid>https://dev.to/sheeterror/div0-function-divide-parameter-2-cannot-be-zero-in-google-sheets-blank-cells-count-as-zero-26md</guid>
      <description>&lt;p&gt;&lt;code&gt;=A2/B2&lt;/code&gt; shows &lt;strong&gt;#DIV/0!&lt;/strong&gt; and the tooltip says &lt;em&gt;Function DIVIDE parameter 2 cannot be zero&lt;/em&gt;. Often B2 isn't even a zero. It's empty. In arithmetic, Google Sheets treats a blank cell as 0.&lt;/p&gt;

&lt;h2&gt;
  
  
  Reproduce it in 30 seconds
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;A (qty)&lt;/th&gt;
&lt;th&gt;B (days)&lt;/th&gt;
&lt;th&gt;C &lt;code&gt;=A/B&lt;/code&gt;
&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;#DIV/0!&lt;/strong&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;&lt;em&gt;(blank)&lt;/em&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;#DIV/0!&lt;/strong&gt;&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Row 4 is the control: same formula, nonzero divisor, no error. So the formula is fine. The divisor is the problem.&lt;/p&gt;

&lt;h2&gt;
  
  
  What it is not
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;Not #N/A.&lt;/strong&gt; Nothing is being looked up.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Not #REF!.&lt;/strong&gt; A2 and B2 still exist.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Not a formula parse error.&lt;/strong&gt; The slash is valid syntax; parse errors come from things like &lt;code&gt;,&lt;/code&gt; vs &lt;code&gt;;&lt;/code&gt; separators.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;&lt;code&gt;AVERAGE&lt;/code&gt; over a range with no numbers can also return #DIV/0!, because it divides a sum by a count of zero. Same error, same idea.&lt;/p&gt;

&lt;h2&gt;
  
  
  Fix 1: guard the divisor with IF (preferred)
&lt;/h2&gt;

&lt;p&gt;A blank equals 0 in &lt;code&gt;B2=0&lt;/code&gt;, so one test covers both broken rows:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(B2=0,"",A2/B2)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Rows 2 and 3 become empty, row 4 still shows 2. Want a label instead? &lt;code&gt;=IF(B2=0,"n/a",A2/B2)&lt;/code&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Fix 2: IFERROR (only if any error should look like "no result")
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IFERROR(A2/B2,"")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;This also hides #REF!, #N/A and #NAME? if the formula breaks in a different way later. That's why the IF version is safer when you care which error you're catching.&lt;/p&gt;

&lt;h2&gt;
  
  
  Fix 3: fix the input
&lt;/h2&gt;

&lt;p&gt;If B2 should never be zero, put the real value in B2 and drop the wrapper. Wrappers are for rows where zero or blank is expected (a new item with no history, a template row). And if B2 is itself a formula that returned 0 when it should have returned 5, fix that formula; &lt;code&gt;IF&lt;/code&gt; won't.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://sheeterror.com/tutorials/div-zero/" rel="noopener noreferrer"&gt;SheetError&lt;/a&gt;, a free library of Google Sheets error fixes (I run it).&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This article was created with the help of AI.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>googlesheets</category>
      <category>spreadsheets</category>
      <category>productivity</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>ISBLANK is FALSE on a cell that looks empty (and IFBLANK doesn't exist) in Google Sheets</title>
      <dc:creator>SheetError</dc:creator>
      <pubDate>Sun, 01 Nov 2026 06:06:14 +0000</pubDate>
      <link>https://dev.to/sheeterror/isblank-is-false-on-a-cell-that-looks-empty-and-ifblank-doesnt-exist-in-google-sheets-50m7</link>
      <guid>https://dev.to/sheeterror/isblank-is-false-on-a-cell-that-looks-empty-and-ifblank-doesnt-exist-in-google-sheets-50m7</guid>
      <description>&lt;p&gt;&lt;code&gt;ISBLANK(B3)&lt;/code&gt; returns &lt;strong&gt;FALSE&lt;/strong&gt;, but B3 looks completely empty. And &lt;code&gt;=IFBLANK(B3,"none")&lt;/code&gt; throws &lt;strong&gt;#NAME?&lt;/strong&gt;. Both are expected behavior. Here's what each "empty" test actually checks.&lt;/p&gt;

&lt;h2&gt;
  
  
  Three cells that all look empty
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;B&lt;/th&gt;
&lt;th&gt;&lt;code&gt;=ISBLANK(B)&lt;/code&gt;&lt;/th&gt;
&lt;th&gt;&lt;code&gt;=B=""&lt;/code&gt;&lt;/th&gt;
&lt;th&gt;&lt;code&gt;=LEN(B)&lt;/code&gt;&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;&lt;em&gt;(never touched)&lt;/em&gt;&lt;/td&gt;
&lt;td&gt;TRUE&lt;/td&gt;
&lt;td&gt;TRUE&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;&lt;code&gt;=""&lt;/code&gt;&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;FALSE&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;TRUE&lt;/td&gt;
&lt;td&gt;0&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;a single space&lt;/td&gt;
&lt;td&gt;FALSE&lt;/td&gt;
&lt;td&gt;&lt;strong&gt;FALSE&lt;/strong&gt;&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;ISBLANK&lt;/strong&gt; asks "is this cell truly empty?" &lt;code&gt;=""&lt;/code&gt; returns an empty &lt;em&gt;text string&lt;/em&gt;, which counts as content, so ISBLANK is FALSE.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;&lt;code&gt;=""&lt;/code&gt;&lt;/strong&gt; asks "does this look like empty text?" TRUE for untouched cells &lt;em&gt;and&lt;/em&gt; &lt;code&gt;""&lt;/code&gt;, FALSE for 0 and for a space.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;A space&lt;/strong&gt; fails both tests. &lt;code&gt;LEN&lt;/code&gt; gives it away. &lt;code&gt;TRIM(B4)=""&lt;/code&gt; is TRUE. (A non-breaking space, &lt;code&gt;CHAR(160)&lt;/code&gt;, needs SUBSTITUTE.)&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  IFBLANK is not a Sheets function
&lt;/h2&gt;

&lt;p&gt;IFBLANK sounds like it should exist next to IFNA and IFERROR, but neither Sheets nor Excel has it, so #NAME? is correct. Use one of these:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(B3="","none",B3)          → "display empty": untouched cells AND formula blanks
=IF(ISBLANK(B2),"type hours",B2)   → "the user hasn't typed anything yet"
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;They are not interchangeable. Pick the one that matches what "empty" means for that column.&lt;/p&gt;

&lt;h2&gt;
  
  
  Return a &lt;em&gt;true&lt;/em&gt; blank from your own formulas
&lt;/h2&gt;

&lt;p&gt;If downstream formulas use ISBLANK, don't output &lt;code&gt;""&lt;/code&gt; or &lt;code&gt;" "&lt;/code&gt;. Leave the argument empty instead:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(A2="",,A2*10)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The omitted argument between the two commas returns a real blank (not &lt;code&gt;""&lt;/code&gt;), so &lt;code&gt;ISBLANK&lt;/code&gt; on that cell is TRUE.&lt;/p&gt;

&lt;h2&gt;
  
  
  Counting blanks disagrees too
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;COUNTA&lt;/code&gt; counts &lt;code&gt;""&lt;/code&gt; formulas as &lt;strong&gt;filled&lt;/strong&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;COUNTBLANK&lt;/code&gt; counts &lt;code&gt;""&lt;/code&gt; as &lt;strong&gt;blank&lt;/strong&gt;.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;If your dashboard mixes COUNTA, COUNTBLANK and ISBLANK, that's why the numbers don't match.&lt;/p&gt;

&lt;h2&gt;
  
  
  Quick diagnosis
&lt;/h2&gt;

&lt;p&gt;ISBLANK is FALSE on an empty-looking cell? Click it and look at the formula bar:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;=""&lt;/code&gt; or a formula → use &lt;code&gt;=""&lt;/code&gt; tests, or return a true blank.&lt;/li&gt;
&lt;li&gt;nothing visible but &lt;code&gt;LEN&lt;/code&gt; &amp;gt; 0 → a space or CHAR(160); clean it.&lt;/li&gt;
&lt;li&gt;#NAME? with IFBLANK → replace with &lt;code&gt;IF(A2="",…)&lt;/code&gt;.&lt;/li&gt;
&lt;/ul&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://sheeterror.com/tutorials/isblank-empty/" rel="noopener noreferrer"&gt;SheetError&lt;/a&gt;, a free library of Google Sheets error fixes (I run it).&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This article was created with the help of AI.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>googlesheets</category>
      <category>spreadsheets</category>
      <category>productivity</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>VLOOKUP returns #N/A but the value is right there: numbers stored as text and trailing spaces</title>
      <dc:creator>SheetError</dc:creator>
      <pubDate>Wed, 28 Oct 2026 22:05:58 +0000</pubDate>
      <link>https://dev.to/sheeterror/vlookup-returns-na-but-the-value-is-right-there-numbers-stored-as-text-and-trailing-spaces-1b7g</link>
      <guid>https://dev.to/sheeterror/vlookup-returns-na-but-the-value-is-right-there-numbers-stored-as-text-and-trailing-spaces-1b7g</guid>
      <description>&lt;p&gt;You can &lt;em&gt;see&lt;/em&gt; the key in the table, and &lt;code&gt;VLOOKUP(..., FALSE)&lt;/code&gt; still says &lt;strong&gt;#N/A&lt;/strong&gt;. That's almost never a missing row. Exact match compares &lt;strong&gt;values&lt;/strong&gt;, not what the cell looks like. The two usual culprits are a number vs text mismatch, and a trailing space.&lt;/p&gt;

&lt;h2&gt;
  
  
  Recreate both
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;A (sku)&lt;/th&gt;
&lt;th&gt;B (name)&lt;/th&gt;
&lt;th&gt;D (lookup)&lt;/th&gt;
&lt;th&gt;E&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;101 &lt;em&gt;(number)&lt;/em&gt;
&lt;/td&gt;
&lt;td&gt;Widget&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;'101&lt;/code&gt; &lt;em&gt;(text)&lt;/em&gt;
&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;=VLOOKUP(D2,A2:B2,2,FALSE)&lt;/code&gt; → &lt;strong&gt;#N/A&lt;/strong&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;apple&lt;/code&gt; &lt;em&gt;(trailing space)&lt;/em&gt;
&lt;/td&gt;
&lt;td&gt;Fruit&lt;/td&gt;
&lt;td&gt;apple&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;=VLOOKUP(D3,A3:B3,2,FALSE)&lt;/code&gt; → &lt;strong&gt;#N/A&lt;/strong&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;h2&gt;
  
  
  Check the keys instead of guessing
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=ISNUMBER(A2)   → TRUE
=ISTEXT(D2)     → TRUE     same digits, different types
=LEN(A3)        → 6
=LEN(D3)        → 5        same word, different lengths
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;Alignment is a hint (text left, numbers right), but ISNUMBER/ISTEXT and LEN are the proof.&lt;/p&gt;

&lt;p&gt;A quick confirmation trick: retype D2 as a plain number (no apostrophe). E2 becomes &lt;em&gt;Widget&lt;/em&gt;. Put the apostrophe back and #N/A returns. That toggle tells you it's a type problem, not a missing row.&lt;/p&gt;

&lt;h2&gt;
  
  
  The trap: TRIM on the wrong side
&lt;/h2&gt;



&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=VLOOKUP(TRIM(D3),A3:B3,2,FALSE)   → still #N/A
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The extra space lives in the &lt;strong&gt;table&lt;/strong&gt;, not in the lookup value. Trimming D3 can't remove it.&lt;/p&gt;

&lt;h2&gt;
  
  
  Fixes
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;Number vs text&lt;/strong&gt; – convert the side that's wrong:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=VLOOKUP(VALUE(D2),A2:B2,2,FALSE)   → Widget
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;If instead the &lt;em&gt;table&lt;/em&gt; holds text and your lookup is a number, convert the lookup with &lt;code&gt;TO_TEXT(D2)&lt;/code&gt; (or clean the table column). Don't wrap both sides and hope.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Space in the table&lt;/strong&gt; – trim the table side:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=VLOOKUP(D3,ARRAYFORMULA({TRIM(A3:A),B3:B}),2,FALSE)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;For real data, a cleaned helper column is easier to maintain. (In locales that use &lt;code&gt;;&lt;/code&gt; as the argument separator, the comma between columns inside &lt;code&gt;{ }&lt;/code&gt; becomes a backslash: &lt;code&gt;{TRIM(A3:A)\B3:B}&lt;/code&gt;.)&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;Still missing after TRIM?&lt;/strong&gt; Check &lt;code&gt;LEN&lt;/code&gt; again: Google's TRIM doesn't remove a non-breaking space, &lt;code&gt;CHAR(160)&lt;/code&gt; (common in data pasted from the web). Turn it into a normal space first: &lt;code&gt;TRIM(SUBSTITUTE(A3,CHAR(160)," "))&lt;/code&gt;.&lt;/p&gt;

&lt;h2&gt;
  
  
  Don't reach for IFNA yet
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;IFNA(VLOOKUP(...),"")&lt;/code&gt; is fine for keys that really aren't there. When the row &lt;em&gt;should&lt;/em&gt; match, hiding the #N/A just hides the type bug. Fix the keys first, then add IFNA for true misses.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://sheeterror.com/tutorials/vlookup-na/" rel="noopener noreferrer"&gt;SheetError&lt;/a&gt;, a free library of Google Sheets error fixes (I run it).&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This article was created with the help of AI.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>googlesheets</category>
      <category>spreadsheets</category>
      <category>productivity</category>
      <category>tutorial</category>
    </item>
    <item>
      <title>Fix "Function ADD parameter 2 expects number values" (#VALUE!) in Google Sheets</title>
      <dc:creator>SheetError</dc:creator>
      <pubDate>Tue, 27 Oct 2026 22:13:03 +0000</pubDate>
      <link>https://dev.to/sheeterror/fix-function-add-parameter-2-expects-number-values-value-in-google-sheets-383f</link>
      <guid>https://dev.to/sheeterror/fix-function-add-parameter-2-expects-number-values-value-in-google-sheets-383f</guid>
      <description>&lt;p&gt;You type &lt;code&gt;=A2+B2&lt;/code&gt;, and instead of a total the cell shows &lt;strong&gt;#VALUE!&lt;/strong&gt;. Hover it and the tooltip says:&lt;/p&gt;

&lt;blockquote&gt;
&lt;p&gt;Function ADD parameter 2 expects number values. But 'pcs' is a text and cannot be coerced to a number.&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;The formula parsed and the function name is fine. Sheets wanted a number and got &lt;strong&gt;text it can't turn into a number&lt;/strong&gt;. Here's how to reproduce it, how to tell it apart from look-alike errors, and three fixes that don't involve IFERROR.&lt;/p&gt;

&lt;h2&gt;
  
  
  Reproduce it in 30 seconds
&lt;/h2&gt;

&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;&lt;/th&gt;
&lt;th&gt;A (qty)&lt;/th&gt;
&lt;th&gt;B (unit)&lt;/th&gt;
&lt;th&gt;C&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;pcs&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;=A2+B2&lt;/code&gt; → &lt;strong&gt;#VALUE!&lt;/strong&gt;
&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;
&lt;code&gt;=A3+B3&lt;/code&gt; → 12&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Same operator, different input type. Two more ways to hit the same error:&lt;/p&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;=VALUE("pcs")&lt;/code&gt; → #VALUE! (VALUE only converts text that already looks like a number, date or time)&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;=DATEVALUE("Widget")&lt;/code&gt; → #VALUE!&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;And the case that surprises people: type &lt;code&gt;'10&lt;/code&gt; (apostrophe, so it's text) in G2, then enter &lt;code&gt;=G2+1&lt;/code&gt; in G3. You get &lt;strong&gt;11&lt;/strong&gt;. Arithmetic &lt;em&gt;will&lt;/em&gt; coerce a text number like &lt;code&gt;'10&lt;/code&gt;. It will not coerce &lt;code&gt;pcs&lt;/code&gt;. That is the whole difference between C2 failing and G3 working.&lt;/p&gt;

&lt;h2&gt;
  
  
  Why SUM doesn't complain but + does
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;=SUM(A2:B2)&lt;/code&gt; returns &lt;strong&gt;10&lt;/strong&gt;, not an error. SUM skips text silently. The &lt;code&gt;+&lt;/code&gt; operator doesn't. So if a total is "too low" with SUM and errors with &lt;code&gt;+&lt;/code&gt;, you have the same root cause: a text cell in a numeric column.&lt;/p&gt;

&lt;h2&gt;
  
  
  It is not one of these
&lt;/h2&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;strong&gt;#N/A&lt;/strong&gt; is a lookup/filter miss. Nothing is being looked up here.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;#NAME?&lt;/strong&gt; is an unknown function name (&lt;code&gt;VLOKUP&lt;/code&gt;). Nothing is misspelled here; the + operator (ADD) rejected its argument.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;#ERROR! / Formula parse error&lt;/strong&gt; means the formula couldn't be read at all (a missing bracket or quote, or commas vs semicolons for your locale). This formula parsed.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;#NUM!&lt;/strong&gt; is a number outside a function's domain (e.g. &lt;code&gt;SQRT(-1)&lt;/code&gt;). Here the type was wrong before the domain mattered.&lt;/li&gt;
&lt;/ul&gt;

&lt;h2&gt;
  
  
  Fixes
&lt;/h2&gt;

&lt;p&gt;&lt;strong&gt;1. Keep units out of the math.&lt;/strong&gt; The quantity column holds numbers only. Put the unit in the header, or add it after the calculation:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=A2&amp;amp;" pcs"      → "10 pcs" (text, for display only)
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;&lt;strong&gt;2. Convert only real text numbers.&lt;/strong&gt;&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=VALUE(G2)                                   → 10 when G2 is '10
=VALUE(REGEXEXTRACT(SUBSTITUTE("1,200.5 pcs",",",""),"[0-9]+(?:\.[0-9]+)?"))   → 1200.5, keeps decimals and drops thousands commas
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;p&gt;The regex takes only the first number, and signs or leading-dot decimals are read wrong without an error (-3.5 becomes 3.5, .5 becomes 5). On comma-decimal locales, swap the separators; see &lt;a href="https://sheeterror.com/tutorials/formula-parse-locale/" rel="noopener noreferrer"&gt;https://sheeterror.com/tutorials/formula-parse-locale/&lt;/a&gt; .&lt;/p&gt;

&lt;p&gt;Don't wrap every #VALUE! in VALUE and hope; &lt;code&gt;VALUE("pcs")&lt;/code&gt; still fails.&lt;/p&gt;

&lt;p&gt;&lt;strong&gt;3. Guard the type when a column may legitimately hold text&lt;/strong&gt; (n/a, pending…):&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight plaintext"&gt;&lt;code&gt;=IF(ISNUMBER(B2), A2+B2, "")
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  Why not IFERROR?
&lt;/h2&gt;

&lt;p&gt;&lt;code&gt;IFERROR(A2+B2, 0)&lt;/code&gt; makes the cell look fine, but it also hides #REF! and #DIV/0! if the formula breaks differently later, and it turns "someone typed pcs in the qty column" into a silent wrong total. Fix the input or test the type with ISNUMBER.&lt;/p&gt;




&lt;p&gt;&lt;em&gt;Originally published on &lt;a href="https://sheeterror.com/tutorials/value-error/" rel="noopener noreferrer"&gt;SheetError&lt;/a&gt;, a free library of Google Sheets error fixes (I run it). There's also a 76-second video walkthrough on the &lt;a href="https://www.youtube.com/@sheeterror" rel="noopener noreferrer"&gt;SheetError YouTube channel&lt;/a&gt;.&lt;/em&gt;&lt;/p&gt;

&lt;p&gt;&lt;em&gt;This article was created with the help of AI.&lt;/em&gt;&lt;/p&gt;

</description>
      <category>googlesheets</category>
      <category>spreadsheets</category>
      <category>productivity</category>
      <category>tutorial</category>
    </item>
  </channel>
</rss>
