How to Sum Excel Cells Containing Less-Than Symbols
Question details
The user needs to calculate row totals for cells containing numbers with comparison symbols (like < or >) by ignoring the symbols and summing the numeric values.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Adding up laboratory results or similar data where numeric values are prefixed with less-than or greater-than signs.
- Observed behavior
- Standard SUM functions ignore these cells because the presence of symbols causes the spreadsheet to treat them as text strings rather than numbers.
Identify the range of cells you want to sum and note all the specific comparison symbols (like < or >) present in your data. Ensure you have a blank cell ready to output the total.
Use SUMPRODUCT and SUBSTITUTE Function
The easiest way to remove a specific symbol and sum the remaining values in a range without altering the original data.
The SUBSTITUTE function strips out the symbol by replacing it with a blank string. The double negative (--) then converts the remaining text string into a numeric value, which SUMPRODUCT successfully totals.
Click on the destination cell where you want the calculated total to appear.
Type =SUMPRODUCT(--SUBSTITUTE(D2:G2,"<","")) in the formula bar, replacing D2:G2 with your actual data range.
Press Enter to calculate the total. If your data contains greater-than signs instead, simply replace the "<" in the formula with ">".
Use SUM, IF, ISNUMBER, and MID
Best for ranges that contain a mix of pure numbers and numbers prefixed with a single-character symbol.
Use LET and BYROW for Dynamic Arrays
Ideal for calculating sums across multiple rows of a matrix simultaneously using modern dynamic array functions.
Easily Calculate and Clean Data with WPS Spreadsheets
WPS Spreadsheets fully supports advanced array formulas, SUBSTITUTE, and SUMPRODUCT functions, allowing you to seamlessly calculate totals from text-formatted laboratory data.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office and open your existing .xlsx or .csv file containing the laboratory results.
- 2. Apply the formula: Select a blank cell and input your preferred SUMPRODUCT or SUBSTITUTE formula from the solutions above.
- 3. Format the output: Press Enter to instantly get the exact sum without manually deleting the comparison symbols in your dataset.
Frequently Asked Questions
Why does the standard SUM function ignore cells with < or > symbols?
Standard SUM functions are designed to calculate only numeric values. When a number is prefixed with a non-numeric symbol like < or >, the spreadsheet engine treats the entire contents of that cell as a text string and skips it during addition.
What does the double negative (--) do in the SUMPRODUCT formula?
The double negative, also known as a double unary operator, forces textual representations of numbers (like the string "10" left over after stripping the < symbol) back into actual numeric values so that mathematical operations can process them.
Can I remove both < and > symbols in a single formula?
Yes, you can nest SUBSTITUTE functions to handle multiple symbols. For example, using =SUMPRODUCT(--SUBSTITUTE(SUBSTITUTE(A1:A5,"<",""),">","")) will remove both less-than and greater-than symbols before summing the range.
Will using these formulas change the original data in my cells?
No, these formulas temporarily extract and calculate the numeric values strictly for the final result. Your original cells containing the symbols will remain completely unchanged.
