Formula to SUM last/previous three values - text in two columns and corresponding values also in two columns

[smide]

Hello.


In columns A and B (A2:A500 and B2:B500) I have a list of product names (text cells) and in columns C and D their corresponding values (numbers in the range from 0 to 1000).
When product is in column A his value is in column C (in the same row of course) and when the product is in column B his value is in the column D.
I need a formula (if it is possible to create such a formula) to SUM values of last three product's occurences (previous three more precisely) and to place result in the columns E or F in the same row where referent product appears.


example.


Calculation only for Product1 in this example.

Sheet1 (current status)

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Product4[/TD]
[TD="align: center"]Product6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Product8[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]45[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Product3[/TD]
[TD="align: center"]Product9[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product8[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Product5[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]7[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]....[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[/TR]
</tbody>[/TABLE]

​

Sheet1 (after calculation)



[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]Results[/TD]
[TD="align: center"]Results[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Product4[/TD]
[TD="align: center"]Product6[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Product8[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Product3[/TD]
[TD="align: center"]Product9[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]Product8[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Product5[/TD]
[TD="align: center"]Product1[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]70[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"]...[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

4th Product1 is in row 7, in column A then his result is in column E also in row 7 : 7+45+2 = 54 (sum of previous three values for Product1)
5th Product1 is in row 8, in column B then his result is in column F also in row 8 : 18+7+45 = 70 (sum of previous three values for Product1)

 

[Eric W]

Try this array formula:
Excel 2012

	A	B	C	D	E	F

<tbody>
[TD="align: center"]1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]Results
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Results
[/TD]

[TD="align: center"]2
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product3
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]2
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]6
[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]3
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product4
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product6
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]9
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]14
[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]4
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product8
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]12
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]45
[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]5
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product2
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]7
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]11
[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]6
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product3
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product9
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]4
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]3
[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]7
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product8
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]18
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]6
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]54
[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]8
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product5
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]5
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]7
[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]70
[/TD]

[TD="align: center"]9
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product6
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product4
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]4
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]9
[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]10
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product1
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product6
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]11
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]13
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]32
[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

[TD="align: center"]11
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product6
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]Product3
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]21
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]18
[/TD]
[TD="bgcolor: #FFFFFF, align: center"]31
[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]
Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]E2
[/TH]
[TD="align: left"]{=IFERROR(INDIRECT(TEXT(LARGE(IF($A$1:$B1=A2,ROW($A$1:$B1)*100+COLUMN($C$1:$D1)),3),"R00C00"),0)+INDIRECT(TEXT(LARGE(IF($A$1:$B1=A2,ROW($A$1:$B1)*100+COLUMN($C$1:$D1)),2),"R00C00"),0)+INDIRECT(TEXT(LARGE(IF($A$1:$B1=A2,ROW($A$1:$B1)*100+COLUMN($C$1:$D1)),1),"R00C00"),0),"")}
[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


Put the formula in E2, then copy to F2, and then down the columns.

If you have the same product in both columns on the same row, you will get uncertain results. Let me know if this works for you.


Edit: I didn't think this shorter version would work, but I tried it and it seems to work just the same:

=IFERROR(SUM(INDIRECT(TEXT(LARGE(IF($A$1:$B1=A2,ROW($A$1:$B1)*100+COLUMN($C$1:$D1)),{1,2,3}),"R00C00"),0)),"")
also with Control+Shift+Enter

 

[smide]

Both formulas work great! Thank you.


Can I just ask you, suppose I need (on some other worksheet with same data as this) SUM of values for product's but this time values from opposite columns like this:

example.


value in cell E7 would be: 11(D5) + 12(C4) + 6(D2) = 29


Value in cell F8: 6(D7) + 11(D5) + 12(C4) = 29 (it turned out by chance the same as E7 here)


Do you have any idea how to solve this?

 

[Eric W]

Sure, in E2 put this variation of the shorter formula:

=IFERROR(SUM(INDIRECT(TEXT(LARGE(IF($A$1:$B1=A2,ROW($A$1:$B1)*100+7-COLUMN($C$1:$D1)),{1,2,3}),"R00C00"),0)),"")

It's a 7 since you're looking at columns 3 and 4, and 7-3=4, and 7-4=3, so it's a way to swap the values.

 

[smide]

Yeah, works perfect. What else can I say...
Thank you so much.