Calculating streaks when some cells are blank

[HBTim67]

I am trying to calculate winning and losing streaks, but some of my cells do not contain information because a team might not play on that current day. How can I have a formula that will over look a blank cell, but still have it continue a streak?

Example

Cell A1. W
A2. L
A3. W
A4.
A5. W
A6. W

I should have a streak of 3 wins, but every time I try a formula it will only calculate it as 2 wins. How do I have a formula overlook the blank cell?

And then I need it to calculate the longest streak as well.


Any and all help would be greatly appreciated

Thank you,

Tim

 

[Marcelo Branco]

In post #1 there are 4 Ws in your data sample and the expected result is 3 (consecutive Ws), So i thought we were talking about consecutive Ws...
To get the number of Ws in the last 10 matches we can use a much simpler formula.

H3
=COUNTIF(INDEX(B:B,F3):INDEX(B:B,G3),"W")

M.

 

[HBTim67]

You are awesome! Thank you so much for all your help.

Tim

 

[Marcelo Branco]

You are welcome.

M.

 

[HBTim67]

Another question. How would I get what the current winning streak would be? In the case using your table it should be 1 since that is the last win in cell B22. What formula would work for current streak?

 

[Marcelo Branco]

How do you define current winning streak? Starting point?

M.

 

[Marcelo Branco]

See if this does what you want

	A​	B​	C​	D​	E​	F​
1​
2​	Game​	Result​	DataFirstRow​	DataLastRow​	LastRow = L​	Current Winning​
3​	1​	W​	3​	25 ​	21​	2​
4​	2​	L​
5​	3​	W​
6​	4​
7​	5​	W​
8​	6​	W​
9​	7​
10​	8​	W​
11​	9​	W​
12​	10​
13​	11​	W​
14​	12​	L​
15​	13​
16​	14​	W​
17​	15​	W​
18​	16​	W​
19​	17​	L​
20​	18​	W​
21​	19​	L​
22​	20​	W​
23​	21​
24​	22​	W​
25​	23​

<tbody>
</tbody>

Given values
C3 = 3
D3 = 25

Array formula in E3
=MAX(IF(INDEX(B:B,C3):INDEX(B:B,D3)="L",ROW(INDEX(B:B,C3):INDEX(B:B,D3)))
Ctrl+Shift+Enter

Regular formula in F3
=COUNTIF(INDEX(B:B,E3):INDEX(B:B,D3),"W")

M.

 

[HBTim67]

Starting with the last game played. So if the last game was a win and you go back from there as in the case of you table the winning streak would be 2 since there was a loss in cell B21

 

[Marcelo Branco]

Is easier to set the starting point after the last L. See above.

M.

 

[HBTim67]

Your help has been fantastic. Everything works.

Again thank ang you for all the help.

Tim

 

[nards]

Hi HBTim67 ,

Try this.

=countif(A1:A6,"W")