I want to make a formula that tracks consecutive wins and losses in my spreadsheet. Here’s what I’m trying to do:
I have a column (let’s call it column Y) on my “Results” sheet that contains profit/loss values. Some are positive numbers (wins) and some are negative (losses).
I need a formula for cell C12 in my “Summary” sheet that will:
Count how many wins in a row I currently have
Also show losing streaks if possible
Basically I want it to look at my results column and tell me if I’m on a 3-win streak, 5-loss streak, etc. The formula should update automatically when I add new results.
Has anyone done something similar? What’s the best approach for this kind of streak calculation?
I used SUMPRODUCT with nested conditions to solve this. It finds your latest entry, then checks backwards to see how many rows have the same win/loss pattern. I used SIGN on your profit/loss values - positive returns 1, negative returns -1. Then SUMPRODUCT counts consecutive rows with matching signs going backwards from your most recent entry. Best part? It handles winning and losing streaks in one formula without helper columns. Just watch out for zeros in your data since SIGN treats them differently. I’ve used this method for months tracking performance - updates instantly when you add new data to the bottom.
Had the exact same problem tracking my trades last year. Here’s what worked: use COUNTIFS with dynamic ranges that look backwards from your latest entry. I started with a helper column marking each result as win/loss (TRUE/FALSE), then built a formula counting consecutive matches from bottom up. Use COUNTIFS with criteria checking win/loss status plus ROW functions for the continuous range. The key trick - reference your last non-empty cell as the starting point, then count backwards until the pattern breaks. New data gets added and the formula automatically recalculates from the new endpoint. Watch out for empty cells though - they’ll mess up your streak counts if you don’t handle them right. Found that out the hard way when my streaks kept resetting for no reason.
Honestly, the easiest way I’ve found is combining IF and INDIRECT functions. Create a formula that checks if your latest result matches the previous one, then keep extending the range until it finds a different result. Something like =IF(SIGN(INDIRECT(“Results!Y”&ROWS(Results!Y:Y)))=SIGN(INDIRECT(“Results!Y”&ROWS(Results!Y:Y)-1))… you get the idea. Bit messy but works great for tracking current streaks without extra columns.