464,727 Members | 1,100 Online Need help? Post your question and get tips & solutions from a community of 464,727 IT Pros & Developers. It's quick & easy.

# Passing Discontinuous Range (Array) of Values to User-Defined Function

 P: n/a What I am trying to do seems simple, but I haven't figured out how to do it. I need to add the contents of the cells in one array, subject to the values in another array. Normally I would utilize one of the SUM functions, such as SUM, SUMIF, or SUMPRODUCT. However, they do not work when the elements of the arrays are on multiple sheets in the Excel workbook. For example: Array 1: Sheet1:Sheet5!A4 (which contains the values: 1, 0, 0, 1, 0) Array 2: Sheet1:Sheet5!B4 (which contains the values: 150, 324, 846, 254, 98) I need to compute the sum of the items in Array 2 whose corresponding items in Array 1 are equal to 1. In the example above, the value I would expect to obtain is 404, which is equal to 150 + 254 (the only items in Array 2 whose corresponding items in Array 1 are equal to 1). I have tried the following VBA code (and several other permutations) to no avail. And I don't understand why the result of the UBound function is zero. Can anyone help me with this? Function ConditionalSum(testArray, ParamArray argumentArray() As Variant) As Double Dim currentRange As Range Dim currentArea As Range Dim i As Integer Dim col As Integer Dim row As Integer Dim rowCount As Integer Dim colCount As Integer Dim areaCount As Integer Dim sum As Double sum = 0# MsgBox ("UBound = " & UBound(argumentArray)) For i = 1 To UBound(argumentArray) Set currentRange = argumentArray(i) Set testRange = testArray(i) rowCount = currentRange.Rows.Count colCount = currentRange.Columns.Count For col = 1 To colCount For row = 1 To rowCount If testRange(row, col) = 1 Then sum = sum + currentRange(row, col) End If Next row Next col Next i ConditionalSum = sum End Function Nov 12 '05 #1 