Sum previous 3 rows matching criteria

[Steven101]

I have a set of data


Column A = Names , Jimmy, Jon etc
Column B = Countries; Spain, Germany etc
Column C = "Yes" of "No"
Column D = Numbers: 1,2,3 etc

In column E, I would like a formula where the if the row in column C = "Yes" it sums the previous 3 scores in column D, matching "Jimmy" in column A and "Spain" in column B.


Hope this makes sense.
Thanks

 

[Scott T]

Is this what you want?

Excel 2010
	A	B	C	D	E
1	names	countries	Y/N	num
2	Jimmy	USA	No	1
3	Jon	Germany	No	2
4	Rick	Spain	Yes	3	0
5	Bob	Canada	No	4
6	Mike	France	Yes	5	0
7	Don	UK	No	6
8	Beth	USA	No	7
9	Jimmy	Spain	yes	8	8
10	pete	Spain	Yes	9	8
11	jack	France	No	10
12	mat	USA	No	11
13	Jimmy	Spain	Yes	12	12
14	Jimmy	Spain	Yes	13	25
Sheet5

Cell Formulas
Range	Formula
E4	=IF(C4="yes",SUMIFS(D2:D4,B2:B4,"spain",A2:A4,"jimmy"),"")
E5	=IF(C5="yes",SUMIFS(D3:D5,B3:B5,"spain",A3:A5,"jimmy"),"")
E6	=IF(C6="yes",SUMIFS(D4:D6,B4:B6,"spain",A4:A6,"jimmy"),"")
E7	=IF(C7="yes",SUMIFS(D5:D7,B5:B7,"spain",A5:A7,"jimmy"),"")
E8	=IF(C8="yes",SUMIFS(D6:D8,B6:B8,"spain",A6:A8,"jimmy"),"")
E9	=IF(C9="yes",SUMIFS(D7:D9,B7:B9,"spain",A7:A9,"jimmy"),"")
E10	=IF(C10="yes",SUMIFS(D8:D10,B8:B10,"spain",A8:A10,"jimmy"),"")
E11	=IF(C11="yes",SUMIFS(D9:D11,B9:B11,"spain",A9:A11,"jimmy"),"")
E12	=IF(C12="yes",SUMIFS(D10:D12,B10:B12,"spain",A10:A12,"jimmy"),"")
E13	=IF(C13="yes",SUMIFS(D11:D13,B11:B13,"spain",A11:A13,"jimmy"),"")
E14	=IF(C14="yes",SUMIFS(D12:D14,B12:B14,"spain",A12:A14,"jimmy"),"")

 

[Steven101]

Thanks for the quick response Scott T. Unfortunately I didn't explain myself very well

Where there is a "Yes" in column C it should sum column D where the for the existing row and the last 2 occasions where "Jimmy" was in column A & "Spain" in column B. So the calc must include the trigger row together with 2 other rows matching the criteria, but not just looking at the last 3 rows of data.


So in your example data if you look at row 14, it would sum D14,D13,D9, returning 45 in cell E14.

Hope that makes more sense.

Thanks

Is this what you want?

Excel 2010

	A	B	C	D	E
names	countries	Y/N	num
Jimmy	USA	No
Jon	Germany	No
Rick	Spain	Yes
Bob	Canada	No
Mike	France	Yes
Don	UK	No
Beth	USA	No
Jimmy	Spain	yes
pete	Spain	Yes
jack	France	No
mat	USA	No
Jimmy	Spain	Yes
Jimmy	Spain	Yes

<colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]6[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]7[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]9[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]10[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]11[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]12[/TD]
[TD="align: right"]12[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]13[/TD]
[TD="align: right"]25[/TD]

</tbody>

Sheet5​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]=IF(C4="yes",SUMIFS(D2:D4,B2:B4,"spain",A2:A4,"jimmy"),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E5[/TH]
[TD="align: left"]=IF(C5="yes",SUMIFS(D3:D5,B3:B5,"spain",A3:A5,"jimmy"),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E6[/TH]
[TD="align: left"]=IF(C6="yes",SUMIFS(D4:D6,B4:B6,"spain",A4:A6,"jimmy"),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E7[/TH]
[TD="align: left"]=IF(C7="yes",SUMIFS(D5:D7,B5:B7,"spain",A5:A7,"jimmy"),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E8[/TH]
[TD="align: left"]=IF(C8="yes",SUMIFS(D6:D8,B6:B8,"spain",A6:A8,"jimmy"),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E9[/TH]
[TD="align: left"]=IF(C9="yes",SUMIFS(D7:D9,B7:B9,"spain",A7:A9,"jimmy"),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E10[/TH]
[TD="align: left"]=IF(C10="yes",SUMIFS(D8:D10,B8:B10,"spain",A8:A10,"jimmy"),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E11[/TH]
[TD="align: left"]=IF(C11="yes",SUMIFS(D9:D11,B9:B11,"spain",A9:A11,"jimmy"),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E12[/TH]
[TD="align: left"]=IF(C12="yes",SUMIFS(D10:D12,B10:B12,"spain",A10:A12,"jimmy"),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E13[/TH]
[TD="align: left"]=IF(C13="yes",SUMIFS(D11:D13,B11:B13,"spain",A11:A13,"jimmy"),"")[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E14[/TH]
[TD="align: left"]=IF(C14="yes",SUMIFS(D12:D14,B12:B14,"spain",A12:A14,"jimmy"),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

try

Excel 2010
	A	B	C	D	E
1	names	countries	Y/N	num
2	Jimmy	Spain	Yes	1	1
3	Jon	Germany	No	2
4	Rick	Spain	Yes	3	1
5	Bob	Canada	No	4
6	Mike	France	Yes	5	1
7	Don	UK	No	6
8	Beth	USA	No	7
9	Jimmy	Spain	yes	8	9
10	pete	Spain	Yes	9	9
11	jack	France	No	10
12	mat	USA	No	11
13	Jimmy	Spain	Yes	12	21
14	Jimmy	Spain	Yes	13	33
Sheet1

Cell Formulas
Range	Formula
E2	{=IF(C2="yes",IFERROR(INDEX($D$2:D2,LARGE(IF($B$2:B2="spain",IF($A$2:B2="Jimmy",ROW($A$2:A2)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D2,LARGE(IF($B$2:B2="spain",IF($A$2:B2="Jimmy",ROW($A$2:A2)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D2,LARGE(IF($B$2:B2="spain",IF($A$2:B2="Jimmy",ROW($A$2:A2)-ROW($A$2)+1)),3)),0),"")}
E3	{=IF(C3="yes",IFERROR(INDEX($D$2:D3,LARGE(IF($B$2:B3="spain",IF($A$2:B3="Jimmy",ROW($A$2:A3)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D3,LARGE(IF($B$2:B3="spain",IF($A$2:B3="Jimmy",ROW($A$2:A3)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D3,LARGE(IF($B$2:B3="spain",IF($A$2:B3="Jimmy",ROW($A$2:A3)-ROW($A$2)+1)),3)),0),"")}
E4	{=IF(C4="yes",IFERROR(INDEX($D$2:D4,LARGE(IF($B$2:B4="spain",IF($A$2:B4="Jimmy",ROW($A$2:A4)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D4,LARGE(IF($B$2:B4="spain",IF($A$2:B4="Jimmy",ROW($A$2:A4)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D4,LARGE(IF($B$2:B4="spain",IF($A$2:B4="Jimmy",ROW($A$2:A4)-ROW($A$2)+1)),3)),0),"")}
E5	{=IF(C5="yes",IFERROR(INDEX($D$2:D5,LARGE(IF($B$2:B5="spain",IF($A$2:B5="Jimmy",ROW($A$2:A5)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D5,LARGE(IF($B$2:B5="spain",IF($A$2:B5="Jimmy",ROW($A$2:A5)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D5,LARGE(IF($B$2:B5="spain",IF($A$2:B5="Jimmy",ROW($A$2:A5)-ROW($A$2)+1)),3)),0),"")}
E6	{=IF(C6="yes",IFERROR(INDEX($D$2:D6,LARGE(IF($B$2:B6="spain",IF($A$2:B6="Jimmy",ROW($A$2:A6)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D6,LARGE(IF($B$2:B6="spain",IF($A$2:B6="Jimmy",ROW($A$2:A6)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D6,LARGE(IF($B$2:B6="spain",IF($A$2:B6="Jimmy",ROW($A$2:A6)-ROW($A$2)+1)),3)),0),"")}
E7	{=IF(C7="yes",IFERROR(INDEX($D$2:D7,LARGE(IF($B$2:B7="spain",IF($A$2:B7="Jimmy",ROW($A$2:A7)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D7,LARGE(IF($B$2:B7="spain",IF($A$2:B7="Jimmy",ROW($A$2:A7)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D7,LARGE(IF($B$2:B7="spain",IF($A$2:B7="Jimmy",ROW($A$2:A7)-ROW($A$2)+1)),3)),0),"")}
E8	{=IF(C8="yes",IFERROR(INDEX($D$2:D8,LARGE(IF($B$2:B8="spain",IF($A$2:B8="Jimmy",ROW($A$2:A8)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D8,LARGE(IF($B$2:B8="spain",IF($A$2:B8="Jimmy",ROW($A$2:A8)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D8,LARGE(IF($B$2:B8="spain",IF($A$2:B8="Jimmy",ROW($A$2:A8)-ROW($A$2)+1)),3)),0),"")}
E9	{=IF(C9="yes",IFERROR(INDEX($D$2:D9,LARGE(IF($B$2:B9="spain",IF($A$2:B9="Jimmy",ROW($A$2:A9)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D9,LARGE(IF($B$2:B9="spain",IF($A$2:B9="Jimmy",ROW($A$2:A9)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D9,LARGE(IF($B$2:B9="spain",IF($A$2:B9="Jimmy",ROW($A$2:A9)-ROW($A$2)+1)),3)),0),"")}
E10	{=IF(C10="yes",IFERROR(INDEX($D$2:D10,LARGE(IF($B$2:B10="spain",IF($A$2:B10="Jimmy",ROW($A$2:A10)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D10,LARGE(IF($B$2:B10="spain",IF($A$2:B10="Jimmy",ROW($A$2:A10)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D10,LARGE(IF($B$2:B10="spain",IF($A$2:B10="Jimmy",ROW($A$2:A10)-ROW($A$2)+1)),3)),0),"")}
E11	{=IF(C11="yes",IFERROR(INDEX($D$2:D11,LARGE(IF($B$2:B11="spain",IF($A$2:B11="Jimmy",ROW($A$2:A11)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D11,LARGE(IF($B$2:B11="spain",IF($A$2:B11="Jimmy",ROW($A$2:A11)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D11,LARGE(IF($B$2:B11="spain",IF($A$2:B11="Jimmy",ROW($A$2:A11)-ROW($A$2)+1)),3)),0),"")}
E12	{=IF(C12="yes",IFERROR(INDEX($D$2:D12,LARGE(IF($B$2:B12="spain",IF($A$2:B12="Jimmy",ROW($A$2:A12)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D12,LARGE(IF($B$2:B12="spain",IF($A$2:B12="Jimmy",ROW($A$2:A12)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D12,LARGE(IF($B$2:B12="spain",IF($A$2:B12="Jimmy",ROW($A$2:A12)-ROW($A$2)+1)),3)),0),"")}
E13	{=IF(C13="yes",IFERROR(INDEX($D$2:D13,LARGE(IF($B$2:B13="spain",IF($A$2:B13="Jimmy",ROW($A$2:A13)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D13,LARGE(IF($B$2:B13="spain",IF($A$2:B13="Jimmy",ROW($A$2:A13)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D13,LARGE(IF($B$2:B13="spain",IF($A$2:B13="Jimmy",ROW($A$2:A13)-ROW($A$2)+1)),3)),0),"")}
E14	{=IF(C14="yes",IFERROR(INDEX($D$2:D14,LARGE(IF($B$2:B14="spain",IF($A$2:B14="Jimmy",ROW($A$2:A14)-ROW($A$2)+1)),1)),0)+IFERROR(INDEX($D$2:D14,LARGE(IF($B$2:B14="spain",IF($A$2:B14="Jimmy",ROW($A$2:A14)-ROW($A$2)+1)),2)),0)+IFERROR(INDEX($D$2:D14,LARGE(IF($B$2:B14="spain",IF($A$2:B14="Jimmy",ROW($A$2:A14)-ROW($A$2)+1)),3)),0),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Steven101]

Thanks Scott T, worked a dream.

Steven