Finding Current Streaks

sebastianmbl

New Member
Joined
Sep 24, 2016
Messages
5
List 1
WeekHomeAwayWinning HomeDraw
1ab10
2cd01
3da01
4bc01
5ac10
6cb01
7ad10
8ac01
9db10
10da01

<colgroup><col><col><col><col span="2"></colgroup><tbody>
</tbody>

List 2
TeamsCurrent Winning StreaksCurrent Draw Streaks
a33
b
c
d

<colgroup><col><col><col></colgroup><tbody>
</tbody>


question 1
Find all "a" in B column (B3:B12) and results in D column (D3:D12) then calculate the current streak ( as Current Streaks is 3 in example)
question 2
Find all "a" in B&C columns (B3:C12) and results in E column (D3:D12) then calculate the current streak ( as Current Streaks is 3 in example)

<colgroup><col span="3"></colgroup><tbody>
</tbody>
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
823
Office Version
  1. 2007
Platform
  1. Windows
sebastianmbl, Good evening.

Your layout:

List 1
ABCDE
1
2WeekHomeAwayWinning HomeDraw
31ab10
42cd01
53da01
64bc01
75ac10
86cb01
97ad10
108ac01
119db10
1210da01

<tbody>
</tbody>


HIJ
1
2TeamsCurrent Winning StreaksCurrent Draw Streaks
3aFormula1Formula2
4b
5c
6d

<tbody>
</tbody>

Try to use:

I3 --> Formula1 --> =SUMIF($B$3:$B$12, H3, $D$3:$D$12)

J3 --> Formula2 --> =SUMIF($B$3:$B$12, H3, $E$3:$E$12) + SUMIF($C$3:$C$12, H3, $E$3:$E$12)

Copy them down.

Result must be:
HIJ
1
2TeamsCurrent Winning StreaksCurrent Draw Streaks
3a33
4b02
5c04
6d13

<tbody>
</tbody>

Is that what you want?
I hope it helps.
 
Upvote 0

Anand Sharma

Board Regular
Joined
Nov 22, 2016
Messages
63
Use the below mentioned code

Current Winning StreaksCurrent Draw Streaks
SUMIF(C4:C13,B19,E4:E13)SUMIF(D4:D13,B19,F4:F13)+SUMIF(C4:C13,B19,F4:F13)

<colgroup><col><col></colgroup><tbody>
</tbody>
 
Upvote 0

Forum statistics

Threads
1,195,858
Messages
6,011,979
Members
441,661
Latest member
Pammie007

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top