Fix SUBTOTAL Returning Zero for Decimal Numbers in WPS Office Spreadsheets
Question details
The user needs to identify why the SUBTOTAL function returns zero when summing decimal values and convert text-formatted decimal entries into numeric values before recalculating SUBTOTAL.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user is summing decimal values with the SUBTOTAL function and expects a numeric total, but the result is zero because the decimal entries are stored as text.
- Observed behavior
- SUBTOTAL returns 0 instead of the decimal sum because the cells are interpreted as text, often due to incompatible decimal separators, thousands separators, currency symbols, hidden characters, or CHAR(160).
Before editing the dataset, confirm which cells are text by selecting a cell and typing =ISTEXT(A1) in an empty cell; TRUE indicates a text value. Also check your system region settings for the expected decimal and thousands separators so you know which characters to replace.
Confirm the Problem with ISTEXT
Use this diagnostic first to find exactly which decimal cells SUBTOTAL is ignoring as text.
The SUBTOTAL function skips text values by design. Cells that look like numbers but are stored as text return TRUE with ISTEXT and are excluded from the sum.
In WPS Office Spreadsheets, open the affected worksheet and select the cell next to the first decimal value, for example B1.
Type =ISTEXT(A1) and press Enter. TRUE means the value in A1 is text; FALSE means it is numeric.
Drag the fill handle down through all rows that SUBTOTAL should sum, then note each cell that shows TRUE.
Clean Hidden Characters and Convert Text Decimals with Text to Columns
Use this when ISTEXT confirms text-formatted decimal values; it removes common hidden characters and converts the cells to real numbers so SUBTOTAL can sum them.
Coerce Stubborn Currency Symbols and Separators with a Helper Formula
Use this if hidden characters or regional separators remain after cleaning, and you prefer a formula-based conversion before SUBTOTAL.
Fix SUBTOTAL Zero Results for Decimal Values in WPS Office Spreadsheets
Convert text-formatted decimal values into real numbers using WPS Office Spreadsheets Find & Replace and Text to Columns tools, then recalculate SUBTOTAL accurately in a free Office-compatible spreadsheet.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the Excel workbook containing the SUBTOTAL decimal issue.
- 2. Confirm the text cells: Click an empty cell and enter =ISTEXT(A1), then drag the formula down to see which decimal cells are text.
- 3. Clean and convert the range: Select the affected decimal cells, press Ctrl+H to replace CHAR(160) or other hidden characters, then go to Data > Text to Columns and click Finish to convert text to numbers.
- 4. Recalculate and save: Update the SUBTOTAL formula to =SUBTOTAL(9,A1:A100) and save the file in .xlsx format to keep it compatible with Microsoft Excel.
Frequently Asked Questions
Why does SUBTOTAL return zero for decimal numbers even though the values are visible in cells?
SUBTOTAL skips text values. If the decimal numbers are stored as text due to a non-breaking space, currency symbol, or incompatible decimal/thousands separator, the function treats them as empty and returns 0. Check a cell with =ISTEXT(A1); TRUE means it is text and must be converted.
How do I find and remove the hidden character causing SUBTOTAL to ignore decimals?
Use =CODE(MID(A1,1,1)) or =LEN(A1) to detect extra hidden characters. The most common is CHAR(160). In WPS Office Spreadsheets, press Ctrl+H, type Alt+0160 in Find what, leave Replace with empty, and click Replace All.
Does changing the cell format from Text to Number fix SUBTOTAL automatically?
No. Cell formatting only changes how a value is displayed, not the underlying data type. After changing the format, you must select the cells and use Data > Text to Columns > Finish, or re-enter each value, to convert text decimals to numeric values.
Can I use a formula to convert comma decimal separators before SUBTOTAL?
Yes. Add a helper column and use =NUMBERVALUE(A1,".",",") if A1 uses a comma as the decimal separator, or =VALUE(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160),""),"$","")) to remove spaces and currency symbols. Then point SUBTOTAL to the helper column.
What SUBTOTAL function number should I use to sum decimal values in WPS Office Spreadsheets?
Use 9 for a normal sum: =SUBTOTAL(9,A1:A100). Use 109 instead of 9 if you also want SUBTOTAL to ignore values in hidden rows.
