Reversing a list in Excel within a formula

Question

So there are a bunch of ways to reverse a list to turn it into a new list, using helper columns. I've written some code that does use helper columns to reverse a list and then use it.

I'm wondering how I would reverse a list without using a helper column for use in a sumproduct - for example,

=sumproduct(Reverse(A1:A3),B1:B3)

Answer 1

This array formula will reverse the order of a vertical array:

= INDEX(B18:B21,N(IF({1},MAX(ROW(B18:B21))-ROW(B18:B21)+1)))

Also, this reverses a horizontal array:

= INDEX(A1:D1,N(IF({1},MAX(COLUMN(A1:D1))-COLUMN(A1:D1)+1)))

---

EDIT

More generally, to vertically flip a matrix instead of just an array (which is just a one-dimensional matrix), use this array formula: (e.g. for range A1:D2)

= INDEX(A1:D2,N(IF({1},MAX(ROW(A1:D2))-ROW(A1:D2)+1)),
              N(IF({1},COLUMN(A1:D2)-MIN(COLUMN(A1:D2))+1)))

And to horizontally flip a matrix, use this:

= INDEX(A1:D2,N(IF({1},ROW(A1:D2)-MIN(ROW(A1:D2))+1)),
              N(IF({1},MAX(COLUMN(A1:D2))-COLUMN(A1:D2)+1)))

And a bonus... to flip a matrix horizontally and vertically in one shot (i.e. rotate it 180 degrees):

= INDEX(A1:D2,N(IF({1},MAX(ROW(A1:D2))-ROW(A1:D2)+1)),
              N(IF({1},MAX(COLUMN(A1:D2))-COLUMN(A1:D2)+1)))

Actually this last one here could more generally be used to flip either a horizontal or vertical array.

Answer 2

This will do what you are asking:

=SUMPRODUCT(INDEX(A:A,N(IF(1,{3;2;1}))),B1:B3)

To make a little more dynamic you can use this array formula:

=SUM(INDEX(A:A,N(IF(1,LARGE(ROW(A1:A3),ROW(A1:A3)))))*B1:B3)

Being an array formula, it needs to be confirmed with Ctrl-Shift-Enter, instead of Enter when exiting Edit mode.