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

smide

smide

Board Regular
Joined
Dec 20, 2015
Messages
164
Office Version
  1. 2016
Platform
  1. Windows
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)
 
Last edited:

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Sort by date Sort by votes
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
 
Last edited:
Upvote 0
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?
 
Upvote 0
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.

:)
 
Upvote 0
Yeah, works perfect. What else can I say...
Thank you so much.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
SUMIF positive & negative values
Replies
3
Views
700
Fluff
Fluff
K
  • Question Question
Populate New or Dropped Data
Replies
3
Views
268
kumara_faith
K
J
  • Solved
Sum up columns based on values (date and time) in more than one column
Replies
3
Views
290
myall_blues
myall_blues
M
  • Solved
return value based on the number that matches any number in three columns
Replies
3
Views
143
MMM_84
M
F
  • Solved
SUMIF and AVERAGEIF two separate columns, only picking the greater value between the two column.
Replies
5
Views
288
hagia_sofia
hagia_sofia

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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
Back
Top