CODEWITHSUNDEEP
excelvbaexcel-formula
Ryland Goldman
Ryland Goldman

Reputation: 105

Excel function to return sum of corresponding items in array

I am pretty new to Excel and VBA, but I was trying to make a function that would provide this result.

    A    B
  -----------
1 | A  |  1 |
2 | B  |  2 |        =TotalItems("A", A1:A4, B1:B4)
3 | C  |  3 |        =5
4 | A  |  4 |
  -----------

It searches through an array to find all occurrences, and then sums up the corresponding values in another array. I'm not sure if there is already a function to do this, but I tried creating one. Here's the code:

Function TotalItems(itemToFind, itemsToReference, resultArr)


    Dim i As Integer
    Dim total As Double
    Dim r As Integer


    For i = 1 To UBound(itemsToReference)

       If StrComp(itemToFind, itemsToReference(i)) = 0 Then
            total = total + CDbl(resultArr(i))
        End If

    Next i


    TotalItems = total


End Function

It returns #VALUE! whenever I run it. I'm not sure what the problem is, and all help will be appreciated.

Upvotes: 0

Views: 481

Answers (1)

Gary's Student
Gary's Student

Reputation: 96753

You are passing Ranges, not arrays, so:

Function TotalItems(itemToFind As String, itemsToReference As Range, resultArr As Range) As Long

    Dim i As Long, rng As Range
    Dim total As Long

    For i = 1 To (itemsToReference.Rows.Count)

       If StrComp(itemToFind, itemsToReference(i, 1)) = 0 Then
            total = total + CLng(resultArr(i, 1))
        End If

    Next i

    TotalItems = total

End Function

enter image description here

(based on your example, I DIMed Long rather than Double)
(note I index the ranges just like 2-D arrays)

In a worksheet cell, you could use:

=SUMPRODUCT(--(A1:A4="A")*(B1:B4))

Upvotes: 1

Related Questions