Calculate the Longest Winning or Losing Streak in Excel
Question details
Calculate the longest consecutive sequence of wins or losses from a column of W and L values.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet contains results in a column, and the user wants the longest uninterrupted run of wins or losses.
- Observed behavior
- The desired result is a number showing the maximum consecutive run of the selected result.
Arrange the results in sequence in one column, using W for wins and L for losses. The examples use A1:A20; adjust the references to match your data, and note that blank cells break a streak.
Calculate the longest streak with a helper column
Use a helper column to see each running streak and avoid entering an array formula.
In cell B1, enter =IF(A1="W",1,0) to start counting wins.
In cell B2, enter =IF(A2="W",B1+1,0), then drag the fill handle down through B20.
In an empty cell, enter =MAX(B1:B20) to return the longest winning streak.
To calculate the longest losing streak, replace "W" with "L" in the formulas in B1 and B2, then fill the B2 formula down through B20 again.

Calculate the longest streak with an array formula
Use a single formula when you do not want a helper column.
Find the longest winning or losing streak in WPS Spreadsheets
Use a helper column in WPS Spreadsheets to count consecutive wins or losses and return the longest streak.
- 1. Open your results: Open the workbook in WPS Spreadsheets and check that the results are in cells A1:A20, or adjust the formulas to your range.
- 2. Start the streak: Select B1 and enter =IF(A1="W",1,0).
- 3. Fill the running count: Enter =IF(A2="W",B1+1,0) in B2, then drag its fill handle down through B20.
- 4. Return the longest streak: Enter =MAX(B1:B20) in an empty cell. For losses, replace "W" with "L" in the helper formulas and fill the B2 formula down again.

Frequently Asked Questions
How do I calculate a losing streak instead of a winning streak?
Replace "W" with "L" in the helper-column formulas or in both tests in the array formula. The maximum result will then represent consecutive losses.
Why does the array formula return an error in older Excel?
Legacy Excel may require array confirmation. Select the formula cell, press F2, and then press Ctrl+Shift+Enter instead of Enter.
Do blank cells count as part of a streak?
No. In these examples, a blank cell breaks the streak because it is not the selected W or L value.
What if my results start below a header?
Adjust the references to the actual data range. For example, if results start in A2, start the helper formula in B2 with =IF(A2="W",1,0), then continue the running count from B3.




