Finding Current Streaks

[sebastianmbl]

List 1
Week	Home	Away	Winning Home	Draw
1	a	b	1	0
2	c	d	0	1
3	d	a	0	1
4	b	c	0	1
5	a	c	1	0
6	c	b	0	1
7	a	d	1	0
8	a	c	0	1
9	d	b	1	0
10	d	a	0	1

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

List 2
Teams	Current Winning Streaks	Current Draw Streaks
a	3	3
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>

 

[Marcílio_Lobão]

sebastianmbl, Good evening.

Your layout:

List 1

	A	B	C	D	E
1
2	Week	Home	Away	Winning Home	Draw
3	1	a	b	1	0
4	2	c	d	0	1
5	3	d	a	0	1
6	4	b	c	0	1
7	5	a	c	1	0
8	6	c	b	0	1
9	7	a	d	1	0
10	8	a	c	0	1
11	9	d	b	1	0
12	10	d	a	0	1

<tbody>
</tbody>

	H	I	J
1
2	Teams	Current Winning Streaks	Current Draw Streaks
3	a	Formula1	Formula2
4	b
5	c
6	d

<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:

	H	I	J
1
2	Teams	Current Winning Streaks	Current Draw Streaks
3	a	3	3
4	b	0	2
5	c	0	4
6	d	1	3

<tbody>
</tbody>

Is that what you want?
I hope it helps.

 

[Anand Sharma]

Use the below mentioned code

Current Winning Streaks	Current 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>