Hello,

I've been trying to create a VBA function that emulates one of my favorite
array functions. The function summarizes a data set for all rows where the
first item in the row matches a supplied value and all columns where the
first item in the column matches another supplied value.

I can create a loop that cylces through the data set summarizing the data I
am seeking, but such an approach is very slow. Accordingly, I've been
seeking a faster solution and have turned to the EVALUATE method to mimic an
array function.

Here's what's I've got so far:

Function TableSum(RowValue, ColumnValue, Ref As Range)
RefText = Ref.Address(external:=True)
RowText = RowValue.Address(external:=True)
ColText = ColumnValue.Address(external:=True)
TableSum = Evaluate("SUM((INDEX(" & RefText & ",0,1)=" & RowText &
")*(INDEX(" & RefText & ",1,0)=" & ColText & ")*IF(ISNUMBER(" & RefText &
")," & RefText & ",0))")
End Function

The funciton works perfectly SO LONG AS all the inputs are from the calling
worksheet. Once I reference ranges on other worksheets, the function does
not work.

Any thoughts? Thanks