Hi,

Will the following work? It calls the SUMPRODUCT_INVERT function from the other thread in the loop.

<pre>Option Explicit

Function MAT(x As Range, y As Range, z As Integer) As Double

Dim fn As WorksheetFunction

Dim LoopCount As Long, i As Long

Dim Rng1 As Range, Rng2 As Range

Set fn = Application.WorksheetFunction

LoopCount = fn.Max(1, x.Rows.Count z)

For i = 1 To LoopCount

With Application.Caller.Worksheet

Set Rng1 = .Range(.Cells(x.Row + (i - 1) * z, x.Column), _

.Cells(x.Row + i * z - 1, x.Column))

Set Rng2 = .Range(.Cells(y.Row + (i - 1) * z, y.Column), _

.Cells(y.Row + i * z - 1, y.Column))

End With

MAT = MAT + SUMPRODUCT_INVERT(Rng1, Rng2)

Next i

End Function

Function SUMPRODUCT_INVERT(Rng1 As Range, Rng2 As Range) As Double

Dim fn As WorksheetFunction

Dim i As Long, Arr1, Arr2

Dim TotalSum As Double

Set fn = Application.WorksheetFunction

If Rng1.Cells.Count <> Rng2.Cells.Count Then

SUMPRODUCT_INVERT = CVErr(xlErrValue)

Exit Function

End If

If Rng1.Cells.Count = 1 Then

SUMPRODUCT_INVERT = Rng1 * Rng2

Exit Function

End If

Arr1 = Rng1.Value

Arr2 = Rng2.Value

For i = LBound(Arr1, 1) To UBound(Arr1, 1)

If IsNumeric(Arr1(i, 1)) And IsNumeric(Arr2(UBound(Arr2, 1) - i + 1, 1)) Then

TotalSum = TotalSum + Arr1(i, 1) * Arr2(UBound(Arr2, 1) - i + 1, 1)

End If

Next i

SUMPRODUCT_INVERT = TotalSum

End Function</pre>

The LoopCount variable uses the Integer division operator, but the board sometimes duplicates it. There should only be one backslash.

Call this as

=MAT(A1:A180,B1:B180,12) for instance.

This requires that there are no blanks in the data. A full 12 months (or z) must be filled in for each loop or this will return erroneous results, as the last few items will be multipled by blanks = 0. That can be fixed as needed, but let me know if this works.

I will leave the worksheet solutions to Aladin and the crowd, as I will bludgeon any attempt to get it working.

BTW, you may be able to use a named formula as an Excel solution. That will give you the speed of Excel and the "easy" function to type that you want.