I have the following example where I found the correct SUMPRODUCT formula but I would like to reverse the order i.e.:
[FONT="]In column A I have different names, each of these names has a corresponding value in column B see example below:[/FONT]
[FONT="]The following formula can look up any name entered in C1 (i.e X) and returns the sum of the last 3 X-Values.
[FONT="]=SUMPRODUCT(B1:B9*(ROW(A1:A9)=LARGE(ROW(A1:A9)*(A1:A9="X"),{1,2,3})))[/FONT][/FONT]
[FONT="]
In the below example for the last 3 X-values the total sum is 3.[/FONT]
[FONT="]
PROBLEM: How can I reverse the order so that in stead of summing up the last three X-Values ( cells A9, A8 and A6) i get the sum of the first three X-values (i.e. values for cells A1, A4 and A6)???
Thank you for all comments and suggestions!
[/FONT]
[TABLE="width: 500"]
<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"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[FONT="]In column A I have different names, each of these names has a corresponding value in column B see example below:[/FONT]
[FONT="]The following formula can look up any name entered in C1 (i.e X) and returns the sum of the last 3 X-Values.
[FONT="]=SUMPRODUCT(B1:B9*(ROW(A1:A9)=LARGE(ROW(A1:A9)*(A1:A9="X"),{1,2,3})))[/FONT][/FONT]
[FONT="]
In the below example for the last 3 X-values the total sum is 3.[/FONT]
[FONT="]
PROBLEM: How can I reverse the order so that in stead of summing up the last three X-Values ( cells A9, A8 and A6) i get the sum of the first three X-values (i.e. values for cells A1, A4 and A6)???
Thank you for all comments and suggestions!
[/FONT]
[TABLE="width: 500"]
<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"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]Z[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Y[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]X[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]