Return Streak Based on Selected Month

Hi guys, would you know how to go about this one? Tricky...

Basically, need formula to return streak values based on a month selected. Attaching an Excel and screenshots below.

Would you be able to suggest? Thanking in advance!

https://www.excelforum.com/attachmen...1&d=1512669595 [EXCEL]

2017-12-07_1957.png

Hi,

Please see the attached of how I went about it.

Used a table with a different layout.

Thanks guys for looking into it.

@ kersplash - sorry, not able to use this suggested option. Needs to be a formula, as per examples (no pivots)
@ Glenn - because 1 is not really a streak? But if formula would return 1 there, that would also be fine
@ kvsrinivasamurthy - your suggested =INDEX($D$4:$O$4,C7) in I7 returns W, when it should be 2?

Originally Posted by vill

Thanks guys for looking into it.

@ kersplash - sorry, not able to use this suggested option. Needs to be a formula, as per examples (no pivots)

It's not a pivot. Uses a formula. Gives the right answer.

Just laid the problem out a different way.

Thanks kersplash. Before replying last time, and this time, I tried using the formulas =IF([@[W/L]]=B22,C22+1,1) to enter in I7 (where it needs to go) and received this error message (screenshot below) So not sure how to use this formula... if you could suggest, would be great! Thank you

2017-12-08_1352.png

See attached row 5

uses same approach as "Kersplash"

=IF(D4=C4,C5+1,1)

Awesome! John, guys, thank you...