UDF needed! Reverse Range/Array Order, then SUMPRODUCT

[mbp257]

I need help creating a UDF to add multiple projects together at discrete points in their life cycle. Each project has the same production forecast.

Please see table below (btw, this is my first time posting on this forum so please let me know if another format is more helpful)

A few notes:
• Column A contains a typical production amount
• Column B contains the schedule or number of projects per period
• Column C represents what linking the cells manually looks like
• Column D contains an excel formula and works correctly but is confusing for others at my company, hence the desire to create a clean, two-argument function

What I imagine the UDF needs to do is first reverse one of the ranges in column A or B, then apply a SUMPRODUCT or equivalent code.

	A	B	C	D
1	'Type' Amount	Schedule	Manual	Formula
2	100	1	100	100
3	90	0	90	90
4	80	2	280	280
5	70	1	=B2*A5+B3*A4+B4*A3+B5*A2	=SUMPRODUCT(A$2:A5, N(OFFSET($B$2:$B5, ROWS($B$2:$B5)- ROW($B$2:$B5)+ ROW($B$2)-1,0)))

<tbody>
</tbody>

Appreciate the help!

 

[Scott Huish]

So all the other cells have the formulas you need, you just need to apply a formula to the last row in Column D?

 

[StephenCrump]

Something like this perhaps:

Function ReverseSumProduct(rng1 As Range, rng2 As Range) As Variant

    Dim v1 As Variant, v2 As Variant
    Dim dTemp As Double
    Dim lrows As Long, r As Long, c As Long
    
    If rng1.Rows.Count <> rng2.Rows.Count _
        Or rng1.Columns.Count <> rng1.Columns.Count Then GoTo ErrorHandler
    On Error GoTo ErrorHandler
    v1 = rng1.Value
    v2 = rng2.Value
    lrows = UBound(v1)
    
    For r = 1 To lrows
        For c = 1 To UBound(v1, 2)
            dTemp = dTemp + v1(r, c) * v2(1 + lrows - r, c)
        Next c
    Next r
    
    ReverseSumProduct = dTemp
    Exit Function

ErrorHandler:
    ReverseSumProduct = CVErr(xlErrValue)

End Function

PS: Welcome to the Forum!

 

[mbp257]

Thanks Stephen! This works great except in the first row when the range is only one row tall [i.e. =ReverseSumProduct(A$2:A2,B$2:B2)]

I end up getting a #VALUE! error. Thoughts!?!

 

[StephenCrump]

Oops, sorry. Try:

 Function ReverseSumProduct(rng1 As Range, rng2 As Range) As Variant

    Dim v1 As Variant, v2 As Variant
    Dim dTemp As Double
    Dim lrows As Long, r As Long, c As Long
    
    If rng1.Rows.Count <> rng2.Rows.Count _
        Or rng1.Columns.Count <> rng2.Columns.Count Then GoTo ErrorHandler
    On Error GoTo ErrorHandler
    v1 = rng1.Value
    v2 = rng2.Value
    
    If rng1.Count = 1 Then
        dTemp = v1 * v2
    Else
        lrows = UBound(v1)
        For r = 1 To lrows
            For c = 1 To UBound(v1, 2)
                dTemp = dTemp + v1(r, c) * v2(1 + lrows - r, c)
            Next c
        Next r
    End If
    
    ReverseSumProduct = dTemp
    Exit Function

ErrorHandler:
    ReverseSumProduct = CVErr(xlErrValue)

End Function

 

[mbp257]

That did it! Thanks!!

 

[StephenCrump]

You're welcome, thanks.

 

[Rick Rothstein]

I need help creating a UDF to add multiple projects together at discrete points in their life cycle. Each project has the same production forecast.

Please see table below (btw, this is my first time posting on this forum so please let me know if another format is more helpful)

A few notes:
• Column A contains a typical production amount
• Column B contains the schedule or number of projects per period
• Column C represents what linking the cells manually looks like
• Column D contains an excel formula and works correctly but is confusing for others at my company, hence the desire to create a clean, two-argument function

What I imagine the UDF needs to do is first reverse one of the ranges in column A or B, then apply a SUMPRODUCT or equivalent code.

	A	B	C	D
1	'Type' Amount	Schedule	Manual	Formula
2	100	1	100	100
3	90	0	90	90
4	80	2	280	280
5	70	1	=B2*A5+B3*A4+B4*A3+B5*A2	=SUMPRODUCT(A$2:A5, N(OFFSET($B$2:$B5, ROWS($B$2:$B5)- ROW($B$2:$B5)+ ROW($B$2)-1,0)))

<tbody>
</tbody>

Appreciate the help!

Since you already have a working formula, why don't we just use it in the UDF...

Function RevSumProduct(Rng1 As Range, Rng2 As Range) As Variant
  RevSumProduct = Evaluate("SUMPRODUCT(" & Rng1.Address & ",N(OFFSET(" & Rng2.Address & ",ROWS(" & _
                  Rng2.Address & ")-ROW(" & Rng2.Address & ")+ROW(" & Rng2(1).Address & ")-1,0)))")
End Function

 

[Rick Rothstein]

Since you already have a working formula, why don't we just use it in the UDF...

Function RevSumProduct(Rng1 As Range, Rng2 As Range) As Variant
  RevSumProduct = Evaluate("SUMPRODUCT(" & Rng1.Address & ",N(OFFSET(" & Rng2.Address & ",ROWS(" & _
                  Rng2.Address & ")-ROW(" & Rng2.Address & ")+ROW(" & Rng2(1).Address & ")-1,0)))")
End Function

A little more compact...

Function RevSumProduct(Rng1 As Range, Rng2 As Range) As Variant
  RevSumProduct = Evaluate(Replace("SUMPRODUCT(" & Rng1.Address & ",N(OFFSET(@,ROWS(" & _
                  "@)-ROW(@)+ROW(" & Rng2(1).Address & ")-1,0)))", "@", Rng2.Address))
End Function

 

[StephenCrump]

Since you already have a working formula, why don't we just use it in the UDF...

I agree, it's usually much better to use Excel native functions.

My approach was partly by design: suspecting that VBA looping might be just as efficient, or perhaps more efficient than, using OFFSET/ROWS to flip the column.

And partly laziness: Wanting to replicate SUMPRODUCT's ability to accommodate arrays, not just columns, and using VBA to avoid figuring out the formula.

Now if things go to script, I'll enjoy a few moments of (mild) smugness before you blow me away with an even more amazing formula .