Calculation Issues

Calculate the Longest Winning or Losing Streak in Excel

Kushani NimanthikaKushani Nimanthika Nov 20, 2026 869 views

Question details

Calculate the longest consecutive sequence of wins or losses from a column of W and L values.

Calculate the Longest Winning or Losing Streak in Excel
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.
Before you start

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.

Solution 1Recommended

Calculate the longest streak with a helper column

Use a helper column to see each running streak and avoid entering an array formula.

1
Start the count

In cell B1, enter =IF(A1="W",1,0) to start counting wins.

2
Continue the count

In cell B2, enter =IF(A2="W",B1+1,0), then drag the fill handle down through B20.

3
Find the maximum

In an empty cell, enter =MAX(B1:B20) to return the longest winning streak.

4
Count losses instead

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 a helper column
Calculate streaks in WPS Spreadsheets

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. 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. 2. Start the streak: Select B1 and enter =IF(A1="W",1,0).
  3. 3. Fill the running count: Enter =IF(A2="W",B1+1,0) in B2, then drag its fill handle down through B20.
  4. 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.
Track consecutive wins or losses with simple formulasOpen and edit Microsoft Excel XLSX and XLS filesInspect running streak counts alongside your results
microsoft office alternative - wps office

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.