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


[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[TD="bgcolor: #DCE6F1"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

Game​

[/TD]
[TD]

Result​

[/TD]
[TD]

DataFirstRow​

[/TD]
[TD]

DataLastRow​

[/TD]
[TD]

LastRow = L​

[/TD]
[TD]

Current Winning​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD]

1​

[/TD]
[TD]

W​

[/TD]
[TD]

3​

[/TD]
[TD]

25
​

[/TD]
[TD]

21​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD]

2​

[/TD]
[TD]

L​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD]

3​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

7​

[/TD]
[TD]

5​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

8​

[/TD]
[TD]

6​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

9​

[/TD]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

10​

[/TD]
[TD]

8​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

11​

[/TD]
[TD]

9​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

12​

[/TD]
[TD]

10​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

13​

[/TD]
[TD]

11​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

14​

[/TD]
[TD]

12​

[/TD]
[TD]

L​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

15​

[/TD]
[TD]

13​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

16​

[/TD]
[TD]

14​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

17​

[/TD]
[TD]

15​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

18​

[/TD]
[TD]

16​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

19​

[/TD]
[TD]

17​

[/TD]
[TD]

L​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

20​

[/TD]
[TD]

18​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

21​

[/TD]
[TD]

19​

[/TD]
[TD]

L​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

22​

[/TD]
[TD]

20​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

23​

[/TD]
[TD]

21​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

24​

[/TD]
[TD]

22​

[/TD]
[TD]

W​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

25​

[/TD]
[TD]

23​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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")