How to Sum the Last Non-Hyphen Values in Each Excel Row in WPS Office
Question details
User needs a formula or VBA solution to sum the last N numeric values in each row while ignoring hyphens and other nonnumeric entries.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A worksheet has rows that mix numeric values and hyphens; the user wants to total only the last N numeric entries in each row.
- Observed behavior
- The desired result is a row-by-row sum of the last N numbers, with hyphens and nonnumeric cells skipped.
Confirm the data range, such as B2:E5, and the cell holding N, such as B7. Ensure numbers are stored as numeric values, not text, and hyphens are entered as text entries.
Use the Excel 365 / WPS Dynamic Array Formula
Best for Excel 365 or WPS Office versions that support FILTER and TAKE; it filters out hyphens and sums the last N numeric values.
This formula assumes the numeric values are in B2:E2 and the count N is in cell $B$7. FILTER removes hyphens, TAKE keeps the last N values from the filtered row, and SUM adds them.
Click cell F2 in the row where you want the sum.
Type =SUM(TAKE(FILTER(B2:E2,B2:E2<>"-"),,-$B$7)) into F2 and press Enter.
Use the fill handle from F2 down through the remaining data rows.
Use an INDEX and LARGE Array Formula for Older Excel Versions
Use this formula in Excel 2019 and earlier when TAKE and FILTER are not available.
Use a VBA SumLastN Function
Use this when you prefer a custom function or need to repeat the calculation across many workbooks.
Sum the Last N Numeric Values Per Row with WPS Office Spreadsheets
Open your Excel workbook in WPS Office Spreadsheets, apply the recommended formula to filter out hyphens and sum the last N numeric values, and keep full compatibility with .xlsx files.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office, click Open, and select the Excel workbook containing the rows with hyphens.
- 2. Locate the result cell and N cell: Click the first result cell, such as F2, and confirm the cell holding N, such as B7.
- 3. Enter the recommended formula: Type =SUM(TAKE(FILTER(B2:E2,B2:E2<>"-"),,-$B$7)) in F2 and press Enter if your WPS Office version supports dynamic arrays.
- 4. Fill the formula down: Drag the fill handle from F2 down through the remaining data rows to calculate each row's sum.
- 5. Save the workbook: Click Menu > Save or press Ctrl+S to save the results in .xlsx format.
Frequently Asked Questions
Can I use these formulas when N is stored in another cell?
Yes. In the examples, N is stored in cell $B$7. Change $B$7 to the actual cell that contains the number of last values you want to sum, and the formula will update automatically.
Will the formula ignore text, blanks, and error values?
FILTER(B2:E2,B2:E2<>"-") removes only hyphen text entries. Blank cells are ignored by SUM, but error values in the range will still cause an error. Clean the range or wrap it with IFERROR if needed.
Does this work in older Excel versions without dynamic arrays?
For Excel 2019 and earlier, use the INDEX and LARGE array formula and confirm it with Ctrl+Shift+Enter. You can also use the VBA SumLastN function.
Why does my dynamic formula return #SPILL! or #CALC!?
If you enter the BYROW version in a range that is not empty, Excel cannot spill the results. Select only the first cell, clear the spill range, and press Enter again.
Can I calculate all rows at once instead of filling down?
Yes. In Excel 365 or WPS versions with BYROW, enter =BYROW(B2:E5,LAMBDA(a,SUM(TAKE(FILTER(a,a<>"-"),,-B7)))) in the first result cell and press Enter.
