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.

[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]'Type' Amount[/TD]
[TD]Schedule[/TD]
[TD]Manual[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]100[/TD]
[TD]1[/TD]
[TD]100[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]90[/TD]
[TD]0[/TD]
[TD]90[/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]80[/TD]
[TD]2[/TD]
[TD]280[/TD]
[TD]280[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]70[/TD]
[TD]1[/TD]
[TD]=B2*A5+B3*A4+B4*A3+B5*A2[/TD]
[TD]=SUMPRODUCT(A$2:A5,
N(OFFSET($B$2:$B5,
ROWS($B$2:$B5)-
ROW($B$2:$B5)+
ROW($B$2)-1,0)))[/TD]
[/TR]
</tbody>[/TABLE]

Appreciate the help!

 

[mbp257]

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

Thank you Rick, I originally tried doing it in a similar way but was spinning my wheels and using WorksheetFunction.