smide
Board Regular
- Joined
- Dec 20, 2015
- Messages
- 162
- Office Version
- 2016
- Platform
- 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)
<tbody>
</tbody>
Sheet1 (after calculation)
<tbody>
</tbody>
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)
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)
A | B | C | D | |
1 | ||||
2 | Product1 | Product3 | 2 | 6 |
3 | Product4 | Product6 | 9 | 14 |
4 | Product8 | Product1 | 12 | 45 |
5 | Product1 | Product2 | 7 | 11 |
6 | Product3 | Product9 | 4 | 3 |
7 | Product1 | Product8 | 18 | 6 |
8 | Product5 | Product1 | 5 | 7 |
9 | .... | .... | ... | ... |
10 | ... | .... | ... | ... |
<tbody>
</tbody>
Sheet1 (after calculation)
A | B | C | D | E | F | |
1 | Results | Results | ||||
2 | Product1 | Product3 | 2 | 6 | ||
3 | Product4 | Product6 | 9 | 14 | ||
4 | Product8 | Product1 | 12 | 45 | ||
5 | Product1 | Product2 | 7 | 11 | ||
6 | Product3 | Product9 | 4 | 3 | ||
7 | Product1 | Product8 | 18 | 6 | 54 | |
8 | Product5 | Product1 | 5 | 7 | 70 | |
9 | ... | ... | ... | ... | ||
10 | ... | ... | ... | ... |
<tbody>
</tbody>
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: