How to reverse the order in a SUMPRODUCT funktion???

[VraGolan]

I have the following example where I found the correct SUMPRODUCT formula but I would like to reverse the order i.e.:

[FONT=&quot]In column A I have different names, each of these names has a corresponding value in column B see example below:[/FONT]
[FONT=&quot]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=&quot]=SUMPRODUCT(B1:B9*(ROW(A1:A9)=LARGE(ROW(A1:A9)*(A1:A9="X"),{1,2,3})))[/FONT][/FONT]
[FONT=&quot]
In the below example for the last 3 X-values the total sum is 3.[/FONT]
[FONT=&quot]
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]

	A	B	C	D
1	X	2	X	3
2	Y	1
3	Z	1
4	X	2
5	Z	3
6	X	1
7	Y	2
8	X	1
9	X	1

<tbody>
</tbody>

 

[sadboy309]

You must click (Ctrl+Shift+Enter) the end of line formula.

{=SUMPRODUCT(B1:B9*(ROW(A1:A9)=SMALL(IF(A1:A9="X",ROW(A1:A9),""),{1,2,3})))}

 

[VraGolan]

@sadboy309 thanks a lot for your help!

My formula has changed a bit and I am trying to apply the same approach to the following formula but Can't get it to work.

=SUMPRODUCT(($C$4:$C$1000=L2)*(ROW($4:$1000)=LARGE(($C$4:$C$1000=L2)*ROW($4:$1000),{1,2,3}))*($I$4:$I$1000))

Do have any suggetion how this formula could be re-written?

Cheers.

 

[sadboy309]

Maybe:

{=SUMPRODUCT(($C$4:$C$1000=L2)*(ROW($4:$1000)=SMALL(IF($C$4:$C$1000=L2,ROW($4:$1000),""),{1,2,3}))*($I$4:$I$1000))}

 

[VraGolan]

YEAH that's working beautifully.
Many Thanks.