Sumproduct with multiplication by first * last instead of first * first

Question

I am looking for some function or formula in Excel 2007 to do the following:

I have two rows of numbers:

1 -1  2 5 10 
1  2 -1 2  5 

I want to do something similar to what sumproduct does, which is to multiple the entries in each column together and then add the totals. However, I want to multiple the first entry in one of the rows by the last entry in the other row, and then the 2nd by the 2nd last, and so on.

So:

1 * 5 + -1 * 2 + 2 * -1 + 5 * 2 + 10 * 1

instead of just

1 * 1 + -1 * 2 + ...

Is there some reasonable way to do this reverse order sum product type of calculation?

I would prefer not to have to create an extra row in reverse order for each of these sum products I want to do.

Answer 1

You can add another row (say on row 3) with this formula:

=INDEX($A$2:$J$2,COUNTA($A$2:$J$2)-COLUMN()+1)

And then do your SUMPRODUCT with the first and third row.

I can't see a way to do it in only one statement

Answer 2

Assuming your examples are placed in A1:E1 and A2:E2, the following will do what you need in a single formula, simply by modifying how the second range is fed to SUMPRODUCT():
(line breaks for clarity and to avoid scroll bars)

=SUMPRODUCT(A1:E1,
            N(OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1))
           )

---

A quick glance at the components:

OFFSET(A2:E2,0,COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1)

This effectively iterates (because it is used as an array by SUMPRODUCT()) over the range A2:E2. It does so on the same row (the 0), but then using the horizontal offset calculated by COLUMNS(A2:E2)-COLUMN(A2:E2)+CELL("Col",A2:E2)-1, which will start at the number of columns in A2:E2 less one (ie, the final entry) and reduce to zero (ie, the first entry), and thus when read as an array it will be read in reverse order.

This is wrapped in an N() call to make sure that any empty cells, text, etc are read as zero and prevents a #VALUE! error in these cases. If you want this to generate errors then don't use the N().

This is then simply dropped in to the SUMPRODUCT() as the second array to be used.

Answer 3

Here's a UDF to get you started.

Function SUMPRODREV(rForward As Range, rBackward As Range) As Double

    Dim i As Long, j As Long
    Dim vaForw As Variant
    Dim vaBack As Variant
    Dim dReturn As Double

    'put range values into arrays
    vaForw = rForward.Value: vaBack = rBackward.Value

    'if only 1 row, multiply columns
    If UBound(vaForw, 1) = 1 Then
        For i = LBound(vaForw, 2) To UBound(vaForw, 2)
            dReturn = dReturn + (vaForw(1, i) * vaBack(1, UBound(vaForw, 2) - (i - 1)))
        Next i
    Else 'if only 1 column, multiply rows
        For i = LBound(vaForw, 1) To UBound(vaForw, 1)
            dReturn = dReturn + (vaForw(i, 1) * vaBack(UBound(vaForw, 1) - (i - 1), 1))
        Next i
    End If

    SUMPRODREV = dReturn

End Function

Answer 4

Here's a single formula that does what you need, but there's a trick to entering it.

Suppose your cell range looks like this. I've named the ranges "top" and "bottom."

1
Highlight any 2 blank cells. In my case, I selected cells B4:C4, like so:

2
Enter this into the formula bar as an array formula (press Ctrl + Shift + Enter):

=SUM(top*INDEX(bottom,1,LARGE(COLUMN(bottom),COLUMN(top))))

Or this (without using named ranges):

=SUM(A1:J1*INDEX(A2:J2,1,LARGE(COLUMN(A2:J2),COLUMN(A1:J1))))

You'll get something like the result above; regular SUMPRODUCT yields 220.

The downside is that you'll just have to hide the extra cell by changing its font color, or by hiding its column or row. The extra cell is necessary to force the 2nd part of the formula (the INDEX part) to "move" or to return an array of values.