How to Calculate Streaks in Excel

Do you want to calculate how long a team’s winning streak is, or how many cells in a row meet certain criteria? In this post, I’ll show you how you can calculate streaks in Excel. Unlike a simple count function, this will require being able to reset your count and go back to zero. I’ll show how this can be done using an easy approach that involves a helper column, and a more challenging way that doesn’t require one.

The easy way to calculate streaks

Here are some results, showing wins (W), losses (L), and ties (T).

The helper column I’m going to create will evaluate the criteria. And the criteria, in this case, will be whether the result is a win. For this, all that’s required is a simple IF statement checking if the value is a W:

=IF(A2="W",1,0)

If the result is a W, the formula will return a value of 1, otherwise, it will be 0:

Next to that column, I will create another one for the actual streak. This formula will look at the criteria column, and if it equals 0, then the streak is 0. If it’s a value of 1, then it will add on to the previous value in the streak column, and thus, add on to it. The formula is as follows:

=IF(B2=0,0,B2+C3)

And that results in the following calculations:

The assumption here is that the earlier results are at the bottom and the most recent games are at the top.

If you wanted to calculate how many games were either won or tied in a row, and thus, an undefeated streak, all you need to do here is to adjust the criteria column. The updated formula would be this:

=IF(OR(A2="W",A2="T"),1,0)

And now the streak values change:

The difficult approach, without helper columns

If you don’t want to use a helper column, calculating streaks is a bit more challenging. You will be using an IF function and checking for criteria, but this time you’ll need to always adjust your starting point (i.e where the streak is 0). And that will need to be within a SUM function to ensure that the values are added. The key to making this work is using the INDIRECT function so that you have control over the exact range you want to include.

Inside that function, I’ll start with column A and use the current row the cell is on, which can be done using the ROW function. Here’s how it starts:

INDIRECT("A"&ROW(B2)&":A"

B2 reflects the first cell in the streak calculation, and it will return a value of 2. The last cell needs to be the last time the streak was broken — when the team recorded a loss. This involves using the MATCH function and searching for an “L”. That formula is as follows:

MATCH("L",INDIRECT("A"&ROW(B2)&":A15"),0)

Here again, I use the ROW function and as my ending cell, I put A15, which is the last cell in the range. This could be adjusted to use a MAX function to make it variable. Since the MATCH function will return a number corresponding to its position within the range (e.g. it won’t return the actual row), I will adjust for the row number immediately above the first cell to be searched. In this case, since I’m searching cells A2:A15, I need to add 1 to ensure I get the row number and adjust for the fact that the MATCH function doesn’t begin from the very first row. I will add all this together into my earlier formula:

INDIRECT("A"&ROW(B2)&":A"&MATCH("L",INDIRECT("A"&ROW(B2)&":A15"),0)+ROW(B1))

Now I have to enclose this within the IF function and check to see if the result is a W:

IF(INDIRECT("A"&ROW(B2)&":A"&MATCH("L",INDIRECT("A"&ROW(B2)&":A15"),0)+ROW(B1))="W",1,0)

Then, I put that all within a SUM function:

SUM(IF(INDIRECT("A"&ROW(B2)&":A"&MATCH("L",INDIRECT("A"&ROW(B2)&":A15"),0)+ROW(B1))="W",1,0))

The one last adjustment that’s necessary is to account for if there is no loss found and the team starts on a winning streak. For this, I’ll add an IFERROR function just before the MATCH function so that if it evaluates to a 0 (after adding the 1), then it will default to the last row (15):

IFERROR(MATCH("L",INDIRECT("A"&ROW(B2)&":A15"),0)+ROW(B1),15)

The full formula for calculating the win streak is the following:

=SUM(IF(INDIRECT("A"&ROW(B2)&":A"&IFERROR(MATCH("L",INDIRECT("A"&ROW(B2)&":A15"),0)+ROW(B1),15))="W",1,0))

Given how complex this formula is, it can get messy if you create too many conditions in it. And if you do have multiple criteria you’re dealing with, then the first approach may be the more practical one to use in that case.

If you liked this post on How to Calculate Streaks in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.