Find the Longest Consecutive Run of Ones in Excel
Question details
Calculate the maximum number of consecutive 1 values in a column of zeros and ones.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A column contains binary values, and the user wants the length of the longest uninterrupted sequence of 1s.
- Observed behavior
- For the sample sequence, the longest run contains four consecutive 1s.
Place the zeros and ones in a single column, such as A1:A15, and identify an empty column for a helper calculation. Adjust the cell references if your data occupies a different range.
Count consecutive ones with a helper column
Use a running count and MAX to find the longest run in Excel without requiring dynamic array functions.
In cell B1, enter =IF(A1=1,1,0) to count the first value.
In cell B2, enter =IF(A2=1,B1+1,0). This adds one for a 1 and resets the count for any other value.
Select B2 and drag its fill handle down through B15 to calculate the run length at each row.
In an empty cell, enter =MAX(B1:B15). For the sample data, the result is 4.

Use a single dynamic array formula in Excel
Use SCAN and LAMBDA when your Excel version supports these functions and you prefer not to use a helper column.
Find Consecutive Ones in WPS Spreadsheets
Use a helper column and the MAX function in WPS Spreadsheets to calculate the longest run of 1s in your data.
- 1. Open your data: Open WPS Spreadsheets and load your workbook, or enter the zeros and ones in A1:A15.
- 2. Start the helper column: In B1, enter =IF(A1=1,1,0).
- 3. Fill the running count: In B2, enter =IF(A2=1,B1+1,0), then drag the fill handle down through B15.
- 4. Calculate the longest run: In an empty cell, enter =MAX(B1:B15) to display the longest consecutive run.

Frequently Asked Questions
What happens if the column contains no ones?
The helper column contains zeros, so =MAX(B1:B15) returns 0.
Does the formula count a run at the end of the column?
Yes. The helper-column and SCAN methods both count a run that continues through the last cell in the selected range.
How do I use the helper-column method with a longer range?
Drag the formula in B2 down to the last data row, then update the MAX range. For example, use =MAX(B1:B100) for data in A1:A100.
What happens if there is a blank cell between ones?
The IF formula treats the blank as a break because it is not equal to 1. Enter a 1 in that cell if it should be part of the run.




