How to find the longest streak in Excel

The helper column: count up, reset on a break

Say you're tracking a study habit, a run of days hitting a sales target, or a team's winning run. The sheet below logs each day with Yes or No in column B. The question is simple: what's the best streak so far, and where am I now?

Start the helper in C2 with =IF(B2="Yes",1,0). In C3, =IF(B3="Yes",C2+1,0) adds one to the cell above while the answer is Yes and drops to 0 on a No. Fill it down. Column C now reads 1, 2, 0, 1, 2, 3, 4, 0 and so on, and the biggest number is the longest streak.

This works in every Excel version, it's easy to check by eye, and it gives you the streak on every row, which is great for a chart. The = comparison ignores case, so the lowercase "yes" on 29 October still counts.

The hidden space that breaks a streak

Look at 30 October: the cell says "Yes " with a space at the end, often left behind by a form or a copy from another app. To Excel that's a different word. =B12="Yes" returns FALSE and =LEN(B12) returns 4, so the helper resets to 0 and a six-day run shows up as 2 and 3.

COUNTIF has the same blind spot: =COUNTIF(B2:B15,"Yes") returns 11, while =SUMPRODUCT(--(TRIM(B2:B15)="Yes")) returns 12. Wrap the cell in TRIM inside the helper, =IF(TRIM(B3)="Yes",C2+1,0), or clean the column once (remove-spaces-in-excel shows how). A drop-down list for column B stops it happening again.

One formula, no helper column: FREQUENCY

When you only want the number, FREQUENCY can do it in one cell. IF(B2:B15="Yes",ROW(B2:B15)) lists the row numbers of the Yes days, and IF(B2:B15<>"Yes",ROW(B2:B15)) lists the rows that break a streak. FREQUENCY counts how many Yes rows fall before each break, which is exactly the length of each run: here {2,4,2,3}. MAX picks the longest.

With TRIM around both B2:B15 references, the stray space stops mattering and the result becomes 6, the true run from 28 October to 2 November. In Excel 2019 and earlier, confirm the formula with Ctrl+Shift+Enter; in Excel 365 a normal Enter works.

To find the longest run of No instead (a losing streak, or days missed), swap "Yes" for "No" in both places. Here it returns 1. Excel 365 users can also build the running count inside one formula with SCAN and LAMBDA, but FREQUENCY works in every version.

When did the streak start and end?

The end date is the date on the row where the helper hits its maximum: =INDEX(A2:A15,MATCH(MAX(C2:C15),C2:C15,0)) returns Monday 26 October. If two streaks tie, MATCH returns the first; =COUNTIF(C2:C15,MAX(C2:C15)) tells you whether there is a tie (here 1, so no).

For the start date, step back by rows, not by days. =INDEX(A2:A15,MATCH(MAX(C2:C15),C2:C15,0)-MAX(C2:C15)+1) returns Thursday 22 October. The tempting shortcut, end date minus 4 plus 1, gives Friday 23 October, which is wrong because Sunday 25 October has no row in the log.

The current streak

The current streak is just the helper's last value: C15 is 3. If the log keeps growing, point at the last filled row instead of a fixed cell: =LOOKUP(2,1/(A2:A100<>""),C2:C100) returns the helper value on the last row with a date, 3 today and whatever it is tomorrow.

Another common formula counts the rows after the last No: =ROWS(B2:B15)-XMATCH("No",B2:B15,0,-1) (Excel 365 and 2021). It returns 6 here, because it only looks for "No" and treats anything else, including "Yes " and blanks, as a Yes. Use it only when the column is clean.

Streaks of consecutive calendar days

A habit tracker often has gaps: no row means you didn't log that day. The rows for 24 and 26 October sit next to each other, but =A8-A7 returns 2, so a strict daily streak should break there. Add a date test to the helper: =IF(TRIM(B3)="Yes",IF(A3-A2=1,D2+1,1),0). A Yes after a gap starts again at 1.

Column D does exactly that. It restarts at 1 on 26 October and finds the longest true daily run, 28 October to 2 November. For work-day streaks that should skip weekends, swap A3-A2=1 for WORKDAY(A2,1)=A3: =WORKDAY(A7,1)=A8 is TRUE, because the next working day after Saturday 24 October is Monday 26 October.