How to Count Consecutive Zeros and Ones in an Excel Column in WPS Office
Question details
The user needs to calculate the lengths of consecutive runs of zeros and ones in a numeric Excel column and return the zero-run lengths and one-run lengths in separate columns.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A user has a column containing only 0s and 1s, such as A1:A15, and wants to analyze how many consecutive zeros and ones appear in each group.
- Observed behavior
- The goal is to produce two spilled result columns showing each consecutive run length for zeros and ones respectively.
Confirm that your values are numeric 0s and 1s in a single contiguous range, such as A1:A15, and that your WPS Office version supports dynamic array functions like LET, FREQUENCY, IF, and FILTER.
Use LET, FREQUENCY, IF, and FILTER to Count Zero and One Runs in WPS Spreadsheets
Use this dynamic array formula when you want a single formula that returns only the consecutive run lengths for zeros, then duplicate it for ones.
The formula groups row positions where the condition is true and uses FREQUENCY to calculate how many rows belong to each consecutive group. It works with dynamic arrays and spills the results automatically.
Click the cell where you want the zero-run lengths to start, for example C2.
Type =LET(ζ,A1:A15,ξ,FREQUENCY(IF(ζ=0,ROW(ζ)),IF(ζ<>0,ROW(ζ))),FILTER(ξ,ξ)) and press Enter. Replace A1:A15 with your actual data range.
Click the adjacent cell, for example D2, type the same formula with ζ=1 instead of ζ=0, and press Enter.
Verify that the spilled run lengths appear in separate columns and that the output cells below are empty to avoid #SPILL! errors.
Calculate Consecutive Zero and One Runs with WPS Office Spreadsheets
WPS Office Spreadsheets supports dynamic array formulas, so you can enter the LET, FREQUENCY, IF, and FILTER formula directly and get the run lengths in separate columns without needing a paid Excel 365-only workflow.
- 1. Open your workbook in WPS Office Spreadsheets: Start WPS Office, open the workbook that contains the column of zeros and ones, and make sure the output area is empty.
- 2. Select the first output cell: Click the cell where you want zero-run lengths to appear, such as C2.
- 3. Enter the dynamic array formula: Type =LET(ζ,A1:A15,ξ,FREQUENCY(IF(ζ=0,ROW(ζ)),IF(ζ<>0,ROW(ζ))),FILTER(ξ,ξ)) and press Enter.
- 4. Duplicate the formula for ones: In the adjacent cell, enter the same formula with ζ=1, then press Enter to create the second spilled result column.
- 5. Review and save: Check that both run-length columns are correct, then save the workbook in .xlsx format for full compatibility.
Frequently Asked Questions
Does this formula work in WPS Office Spreadsheets or only in Microsoft Excel?
It works in WPS Office versions that support dynamic arrays. Open WPS Spreadsheets, enter the formula in a blank cell, and ensure no data is blocking the spill range.
How do I change the formula to count runs for a different value?
Replace the condition ζ=0 with ζ=1, or use your target value such as ζ=5. Keep the FREQUENCY row grouping parts unchanged.
Why do I get a #SPILL! error after entering the formula?
The formula spills results into adjacent cells. Clear or delete any existing data in the cells directly below and to the right of the formula cell, then press Enter again.
Can I use this method if my zeros and ones are in a row instead of a column?
The formula uses ROW(ζ), so it is designed for a vertical column range. For a horizontal range, adjust the row-index logic or use TRANSPOSE to convert it to a column first.
