When you export the results of a massive Google Form or Typeform survey into a Google Sheet, the raw data is almost always contaminated. If you asked 500 customers, “How many products did you buy this year?”, you want clean, mathematical integers (like 12, 5, or 0) so you can run a SUM() formula to calculate the total.
However, humans are terrible at following instructions. Instead of typing “12”, a customer will type “Twelve”. Another will type “I bought 5”. Another will just type “N/A”. If you try to run a mathematical formula on a column containing a mixture of real numbers and conversational text, Google Sheets will instantly crash and output a massive #VALUE! error.
To safely calculate totals without manually reading 500 rows of data, you must use the ISNUMBER function to programmatically interrogate every single cell, isolate the actual math, and completely ignore the garbage text.
The Syntax of ISNUMBER
The ISNUMBER function is a simple boolean test. You point it at a cell, and it outputs TRUE if the cell contains pure, usable mathematics, and FALSE if it contains text, errors, or mixed alphanumeric strings (like “5 apples”).
=ISNUMBER(value)
Step 1: Build a Sanitized Extraction Column
Assume your dirty survey answers are in Column A.
- Click on cell B2 (an empty column next to the data).
- We are going to combine an
IFstatement with theISNUMBERtest. Paste this exact formula:=IF(ISNUMBER(A2), A2, 0) - Press Enter.
- Click the small blue square in the corner of B2 and drag it down to the bottom of the sheet.
How This Clever Formula Works
This formula acts as a ruthless bouncer for your spreadsheet.
- It looks at cell A2. If A2 contains the pure number
12, ISNUMBER returns TRUE. Because it is TRUE, the IF statement allows the number12to pass straight through into Column B. - If it looks at cell A3, and A3 contains the text “I bought 5”, ISNUMBER aggressively flags it as FALSE (because text ruins math). Because it is FALSE, the IF statement instantly intercepts the text, destroys it, and replaces it with a clean, mathematical
0in Column B.
Step 2: Safely Calculate Your Totals
Now, instead of trying to run a SUM formula on the dirty, unpredictable Column A, you run your SUM formula on the pristine, mathematically perfect Column B.
=SUM(B:B)
Google Sheets will instantly tally up all the legitimate, valid responses, while cleanly ignoring all the conversational garbage, saving you hours of manual data entry cleanup.