Thread: How to Count and/or Sum consecutive values on a range with blank data in the middle

Hi....my first post here....will try to make it simple.

I have a trading system spreadsheet where I want to calculate Consecutive Day Wins / Loss, as well as the money won or loss by those consecutive days.

IF I would have valid data (call it a WIN or LOSS) EVERY day, I know how to do this in some way (maybe not the ideal way). BUT the problem I face is there are many days that there is no trade (no value win/loss), so the concept of consecutive is not really consecutive days, but consecutive days WITH TRADES. Not sure how to do this.

The real output I need is the MAX number of consecutive wins (and the $ it represents) as well as for losses.

I attached the example on the file to visualize it better.

Thanks a lot.

Originally Posted by Marcol

Maybe this way?
Use a helper column, say Column E, this can be hidden (use the grouping icon/button)
In E1 enter 1
In E2

=IF(OR(AND(D3="W",D2="L"),AND(D3="L",D2="W"),D3=""),ROW(),"")

In F2

=IF(ISNUMBER($E2),SUM(INDEX(C:C,MAX($E$1:$E1)+1,1):INDEX(C:C,ROW(),1)),"")

Drag/Fill both Down to the last row of your table.

Thanks for your time.

For the $ part it's ok, still I need to have the numbers of consecutive days of W / L, so where we have the 13 on your formula, it should say 6 (as there were six consec TRADING days without Losses).

Yest night I found some type of solution to this, but as always is A WAY I managed to do it with many helper cols, but would love to know if some more complex formulas I do not know can optimize this as I like learning new tips.



Attached is the solution I got.

Originally Posted by Marcol

Add this
In G2

=IF(ISNUMBER(E3),D3,"")

In H2

=IF(ISNUMBER($E3),MAX(COUNTIF(INDEX(D:D,MAX($E$2:$E2)+1,1):INDEX(D:D,ROW(),1),"W"),COUNTIF(INDEX(D:D,MAX($E$2:$E2)+1,1):INDEX(D:D,ROW(),1),"L")),"")

Drag/Fill both down.

See the C/F for the cell colour rules.

Thansk a lot.....will study all the index funtion u use there....