Count Occurrences of the Longest Consecutive Run of 1s in Excel
Question details
Find the longest consecutive run of 1 values in a spreadsheet row and count how many separate runs have that maximum length.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Values are arranged horizontally in A2:U2. The maximum run length is calculated in W2, and the number of runs with that length is needed in X2.
- Observed behavior
- The existing FREQUENCY formula returns the longest run length but does not report how many times that length occurs.
Confirm that the values to analyze are in A2:U2 and reserve W2 and X2 for the results. The TEXTJOIN method requires numeric 0 and 1 values with no blank cells; use the FREQUENCY counting method if blanks or other values should break a run.
Count maximum-length runs with FREQUENCY
Use this method to count separate runs of numeric 1s, treating any value other than 1 as a break.
FREQUENCY calculates the lengths of consecutive runs. W2 stores the largest length, and X2 counts how many of those lengths equal W2. The zero check returns 0 when there are no 1s.
Select W2 and enter =MAX(FREQUENCY(IF($A2:$U2=1,COLUMN($A2:$U2)),IF($A2:$U2<>1,COLUMN($A2:$U2)))). In dynamic-array Excel, press Enter. In older Excel versions, confirm with Ctrl+Shift+Enter.
Select X2 and enter =IF(W2=0,0,SUM(IF(FREQUENCY(IF($A2:$U2=1,COLUMN($A2:$U2)),IF($A2:$U2<>1,COLUMN($A2:$U2)))=W2,1,0))). Use Enter in dynamic-array Excel or Ctrl+Shift+Enter in older versions.
Check W2 for the longest run length and X2 for its occurrence count. For example, a row containing 1,1,0,1,1 returns 2 in both cells. Select W2:X2 and drag the fill handle downward to analyze additional rows.
Count maximum-length runs with TEXTJOIN and SUBSTITUTE
Use this shorter counting formula when the row contains only numeric 0s and 1s, has no blanks, and your Excel version supports TEXTJOIN.
Count the longest runs of 1s in WPS Spreadsheet
Use WPS Spreadsheet to calculate the maximum run length and count matching runs with FREQUENCY-based array formulas. You can work directly with an Excel workbook and keep the results in separate cells.
- 1. Open the workbook: Open WPS Spreadsheet, choose Open, and select your Excel workbook. Locate the row of numeric values in A2:U2.
- 2. Find the longest run: Select W2 and enter =MAX(FREQUENCY(IF($A2:$U2=1,COLUMN($A2:$U2)),IF($A2:$U2<>1,COLUMN($A2:$U2)))). Confirm with Ctrl+Shift+Enter if your version requires array-formula entry.
- 3. Count matching runs: Select X2 and enter =IF(W2=0,0,SUM(IF(FREQUENCY(IF($A2:$U2=1,COLUMN($A2:$U2)),IF($A2:$U2<>1,COLUMN($A2:$U2)))=W2,1,0))). Confirm as an array formula if required.
- 4. Verify and save the results: Check the results against a short row such as 1,1,0,1,1, which should return 2 in W2 and X2. Choose Save As and select .xlsx to retain an Excel-compatible workbook.
Frequently Asked Questions
What is the difference between the longest run length and its occurrence count?
For 1,1,1,0,1,1,1, the longest run length is 3, and it occurs twice. W2 reports 3; X2 reports 2.
Why does TEXTJOIN give the wrong count when my row contains blanks?
TEXTJOIN("",1,A2:U2) ignores empty cells. A sequence such as 1, blank, 1 becomes "11", merging two separate runs. Use the FREQUENCY counting formula in X2 when blanks should break a run.
What should the formulas return if the row contains no 1s?
W2 should return 0, and X2 should also return 0. The FREQUENCY counting formula checks W2=0 explicitly; the TEXTJOIN formula uses IFERROR to return 0 when the divisor is zero.
Why am I getting a #NAME? error with TEXTJOIN?
Your Excel version may not support TEXTJOIN, or the function name may be misspelled. Select X2 and replace the text-based formula with the FREQUENCY counting formula, using Ctrl+Shift+Enter if your Excel version requires legacy array entry.
Can I use a different row range or copy the formulas down?
Yes. Replace every A2:U2 reference in both formulas with your actual horizontal range. To analyze additional rows, select W2:X2 and drag the fill handle downward; the row references adjust automatically.
