Excel function to return sum of corresponding items in array

Question

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.

FYI the result you want can be achieved without VBA, using =SUMIFS(B1:B4,A1:A4,"A") or =SUMIFS(B:B,A:A,"A") — chris neilsen
– chris neilsen, Commented Jun 3, 2020 at 2:19

Gary's Student · Accepted Answer · 2020-06-03 00:50:20Z

1

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

(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))

edited Jun 3, 2020 at 0:50

answered Jun 3, 2020 at 0:41

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel function to return sum of corresponding items in array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related