How to Sum Digits from Every Sixth Excel Column in WPS Spreadsheets
Question details
The user needs to process values in every sixth column of a row, extract each digit position from those values, and return the sums of each position in a vertical range.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a row of numeric entries and needs to aggregate digits across every sixth column, preserving or ignoring leading zeros as appropriate.
- Observed behavior
- The expected result is a vertical spill range containing the sum of each digit position.
Confirm that your WPS Spreadsheets version supports the dynamic array functions CHOOSECOLS, SEQUENCE, BYROW, and LAMBDA. If any function is unavailable, use the VBA alternative on a desktop version of WPS Office.
Use a Dynamic Array Formula with CHOOSECOLS, TEXT, MID, and BYROW
Best for WPS Spreadsheets versions that support dynamic arrays; this formula selects every sixth column, converts values to six-digit text, splits digits, converts them to numbers, and sums each digit position automatically.
The formula works in WPS Spreadsheets versions that support Microsoft 365-style dynamic array functions. It treats nonnumeric entries as zero and uses TEXT with '000000' to keep leading zeros before digit extraction.
Click the cell where the vertical result should start, for example A2, and open the formula bar.
Type =BYROW(IFERROR(--MID(TEXT(CHOOSECOLS(F1:ZZ1,SEQUENCE(INT((COLUMNS(F1:ZZ1)+5)/6),,,6)),"000000"),{1;2;3;4;5;6},1),0),LAMBDA(r,SUM(r))) in the formula bar and press Enter.
Verify that WPS Spreadsheets fills one result per digit position in the rows below the entered cell.
Change F1:ZZ1 to your actual data range, and update "000000" and {1;2;3;4;5;6} if you need more than six digit positions.
Use a VBA Macro to Sum Each Digit Position
Use this alternative when dynamic array functions are not available or when you prefer a macro that writes the sums directly to column A.
Use WPS Spreadsheets to Sum Digits from Every Sixth Column
Download WPS Office to use Excel-compatible dynamic array formulas that select every sixth column, split each number into digit positions, and return the sums in a vertical range.
- 1. Download and install WPS Office: Go to the official WPS Office website, download the installer, and complete the installation.
- 2. Open the workbook in WPS Spreadsheets: Launch WPS Spreadsheets and open the workbook that contains the row of values you want to process.
- 3. Enter the formula in the formula bar: Select an output cell and enter the CHOOSECOLS, TEXT, MID, and BYROW formula in the formula bar, replacing F1:ZZ1 with your actual range.
- 4. Press Enter and review the spill range: Press Enter to return the digit-position sums in a vertical spill range, then save the file in .xlsx or another Excel-compatible format.
Frequently Asked Questions
Does this formula work in WPS Spreadsheets?
Yes, if your WPS Spreadsheets version supports dynamic array functions such as CHOOSECOLS, SEQUENCE, BYROW, and LAMBDA. If you see #NAME?, update WPS Office or use the VBA macro alternative.
Why does the formula use TEXT with "000000"?
The TEXT function formats each selected value as six-digit text so leading zeros are retained. This makes MID extract every digit position consistently.
What should I change if my data has more than six digits?
Increase the format string from "000000" to the required number of zeros and change {1;2;3;4;5;6} to the corresponding sequence of digit positions.
Can I sum every sixth column in a different row or sheet?
Yes. Replace F1:ZZ1 with the actual range, such as 'Sheet2'!B2:ZZ2, and keep the formula entered in a cell with enough room to spill downward.
How do I treat blank cells or text as zero?
The IFERROR function wraps the digit extraction, so nonnumeric or missing digits are converted to 0 before BYROW sums them.
