How to Return the Length of Consecutive Value and Blank Groups in WPS Spreadsheets
Question details
The user needs a formula that counts each consecutive group of X values and blank cells across a row and returns the group lengths in order, such as 3 x, 6 empty, 1 x.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is analyzing a row where groups of X markers and blanks alternate and wants a compact text result showing the size of every consecutive group.
- Observed behavior
- The user needs to return ordered group lengths rather than only counting total X values or total blanks.
Make sure the data is in a single row and that the formula range matches the exact columns you want to analyze. Confirm that your WPS Office version supports dynamic-array functions such as LET, LAMBDA, TEXTJOIN, HSTACK, and TOCOL.
Use a LET and LAMBDA dynamic-array formula to list consecutive X and blank group lengths
Use this when your WPS Office version supports dynamic-array functions and you want one formula to return ordered group lengths, such as 3 x, 6 empty, 1 x.
This formula uses FREQUENCY to count runs of equal values, then a LAMBDA helper labels X groups and blank groups, and HSTACK with TOCOL interleaves the results in the original group order.
Open WPS Spreadsheets, go to the worksheet containing the row, and click an empty cell where you want the group-length result to appear.
If your data is in A1:AG1, type or paste this formula into the formula bar: =LET(a,A1:AG1,columns,COLUMN(a),groups,LAMBDA(value,label,LET(counts,FREQUENCY(IF(a=value,columns),IF(a<>value,columns)),FILTER(counts,counts>0,0)&label)),xGroups,groups("x"," x"),blankGroups,groups(""," empty"),TEXTJOIN(", ",,TOCOL(HSTACK(IF(a="",blankGroups,xGroups),IF(a="",xGroups,blankGroups)),2)))
Replace A1:AG1 with your actual row range. If your marker is uppercase X instead of lowercase x, change the value inside groups("x"," x") to match it.
Press Enter. The cell returns a comma-separated list such as 3 x, 6 empty, 1 x in the order the groups appear across the row.
Count Consecutive Value and Blank Groups with WPS Spreadsheets
Use WPS Spreadsheets to calculate the length of every consecutive X and blank group in a row with a single dynamic-array formula. WPS Office supports the same LET, LAMBDA, FREQUENCY, HSTACK, and TOCOL functions used in Excel, so you can get the ordered result without manual work.
- 1. Open WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the workbook that contains the row you want to analyze.
- 2. Select the result cell: Click an empty cell where you want the ordered group-length text to appear.
- 3. Enter the formula: Type or paste the dynamic-array formula in the formula bar, replace A1:AG1 with your actual range, and press Enter.
- 4. Check the returned list: Review the comma-separated output such as 3 x, 6 empty, 1 x and confirm it matches the visible runs in the row.
Frequently Asked Questions
Does this formula work in both WPS Spreadsheets and Excel?
Yes, if the version supports dynamic-array functions such as LET, LAMBDA, TOCOL, and HSTACK. In WPS Spreadsheets or Excel 365, paste the formula into the formula bar and press Enter.
How do I change the formula if my data is in a different row or a column?
Replace A1:AG1 with your own range. For a vertical column, change COLUMN(a) to ROW(a) and replace HSTACK with VSTACK, then adjust the final TOCOL arrangement so groups are read in column order.
Why does the formula return #NAME? or #VALUE!?
#NAME? usually means LET, LAMBDA, TOCOL, or HSTACK is not available in your current WPS Office version. #VALUE! can occur if the range contains values other than x or blank, because the groups helper only checks for x and empty cells.
Can I change the labels x and empty in the result?
Yes. In the groups call, change "x" to the value you are counting and change " empty" to the label you want for blank runs. Make sure the comparison value matches the cell contents exactly.
