How to Sum Numbers from Text-Based Dropdown Values in Excel
Question details
The user needs to extract numeric values embedded in text strings within dropdown selections (e.g., '1-good', '2-best') and calculate their total automatically without manual data entry.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating a total score based on survey or dropdown responses where each option contains both a numerical value and descriptive text separated by a delimiter.
- Observed behavior
- Standard SUM or SUMIF functions return zero or fail because the values are formatted as text strings. The numbers must be isolated and converted to numerical formats before they can be summed.
Ensure your text-based dropdown values follow a consistent pattern, such as always starting with a number followed by a specific delimiter like a hyphen (e.g., '1-good').
Use TEXTBEFORE and SUMPRODUCT (Modern Excel Versions)
Extracts the number appearing before a specific character (like a hyphen) and calculates the sum across the entire range.
If you are using a newer version of Excel or WPS Spreadsheet that supports dynamic array functions, the TEXTBEFORE function provides the cleanest way to split the text.
Click on the cell where you want the final calculated total to appear.
Type the formula: =SUMPRODUCT(IFERROR(--TEXTBEFORE(B2:D2,"-"),0)). Make sure to replace 'B2:D2' with the actual range containing your dropdown selections.
Press Enter. The formula will extract the digits before the hyphen, convert them into actual numbers using the double negative (--), and sum them together.
Use LEFT, FIND, and SUMPRODUCT (Older Excel Versions)
A highly compatible alternative method for older Excel versions that lack the TEXTBEFORE function.
Extract and Sum Text-Based Numbers with WPS Spreadsheet
WPS Spreadsheet provides powerful array calculation capabilities and comprehensive formula support, allowing you to easily separate numbers from text and calculate complex totals just like in Microsoft Excel.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your text-based dropdowns.
- 2. Select the sum cell: Click on the blank cell where you want the aggregated total.
- 3. Enter the formula: Type =SUMPRODUCT(IFERROR(--LEFT(B2:D2,FIND("-",B2:D2)-1),0)) and press Enter.
- 4. View your total: WPS will instantly calculate and display the sum of the extracted numbers.
Frequently Asked Questions
Why does my SUMIF formula return zero when trying to sum text with numbers?
SUMIF can use wildcards (like '*-good') to count or sum corresponding ranges, but it cannot mathematically add the numbers embedded directly inside those text strings. You must extract the numbers first using text functions like LEFT or TEXTBEFORE, and convert them to numeric values.
What does the double hyphen (--) do in these extraction formulas?
The double unary operator (--) coerces a text representation of a number (like the string '1' extracted from '1-good') into a true numeric value. Without it, the SUMPRODUCT function would ignore the extracted characters because it views them as text, not math-ready numbers.
How do I sum the values if the number is at the end of the text (e.g., 'good-1')?
You can use the TEXTAFTER function instead of TEXTBEFORE. Alternatively, you can use the RIGHT function combined with FIND or LEN to extract the numerical characters from the end of the text string.
Can I use the SUBSTITUTE function to extract numbers for summing?
Yes. If your text portion is consistent and you know exactly what words are being used, you can use the SUBSTITUTE function to replace the descriptive text and delimiters with a blank space (""), leaving only the number behind to be summed.
