Reversing the order in a sumproduct

[Tkeller]

Hello,

I am sure this has been asked before. What I am trying to do is to reverse the order for a sumproduct:

Sumproduct( A1:A3,B1:B3) == A1*B1 + A2*B2 + A3*B3

Instead, what I'm after is

Sumproduct( A1:A3, Reverse(B1:B3)) == A1*B3 + A2*B2 + A3*B1

Thank you!

 

[DRSteele]

There is probably a superior way, but this works at least.

[FONT=Verdana]=SUMPRODUCT(A1:A3,INDEX(B1:B3,LARGE(ROW(B1:B3)-ROW(B1)+1,ROW(INDIRECT("1:"&COUNT(B1:B3))))))[/FONT]

 

[Tetra201]

Here is my take on it:

=SUMPRODUCT(A1:A3,INDEX(B1:B3,N(IF(,,LARGE(ROW(B1:B3)-ROW(B1)+1,ROW(B1:B3)-ROW(B1)+1)))))

Note: This is an array formula; it has to be entered using Ctrl+Shift+Enter, not just Enter

 

[Tetra201]

Here is a more compact version:

=SUMPRODUCT(A1:A3,INDEX(B1:B3,N(IF(,,ROW(B1)+ROWS(B1:B3)-ROW(B1:B3)))))

Note: This is an array formula; it has to be entered using Ctrl+Shift+Enter, not just Enter

 

[Tkeller]

Thank you very much I will give this a try!

 

[Tkeller]

Well, I appreciate the formula, but my data is in rows. So what I need to do is to sumproduct a row with the reverse of another row. Its my mistake that I gave the example assuming columns. Please could you help me to convert your solution to one that handles rows? I tried to, but I am no expert!

Many thanks!

 

[Tetra201]

Here is a row-handling version of the formula:

=SUMPRODUCT(A1:C1,INDEX(A2:C2,N(IF(,,COLUMN(A2)+COLUMNS(A2:C2)-COLUMN(A2:C2)))))

Note: This is an array formula; it has to be entered using Ctrl+Shift+Enter, not just Enter

 

[Peter_SSs]

Does this standard-entry formula do what you want?

Excel Workbook
	A	B	C	D	E	F
1	1	3	5	4		35
2	2	2	4	5
Reverse Sumproduct

 

[Tkeller]

Wow. That is exactly what I needed. Thanks to all, I am really starting to understand some of these things.

 

[Peter_SSs]

Wow. That is exactly what I needed. Thanks to all, I am really starting to understand some of these things.

Good news. Thanks for letting us know.