CODEWITHSUNDEEP
vbaexcel
Mottster
Mottster

Reputation: 137

Excel SUMPRODUCT with VBA based conditions

Does anyone know how to use a VBA function within a worksheet based call to SUMPRODUCT?

This works fine, summing the values in column N where column L contains "Y" and col A contains a different value to col K...

=SUMPRODUCT(--(Input!L1:L100="Y"), --(Input!A1:A100<>Input!K1:K100), Input!N1:N100)

But I want to be able to apply more logic than just A<>K in my second criteria, something like this...

=SUMPRODUCT(--(Input!L1:L100="Y"), --(MatchNames(Input!A1:A100,Input!K1:K100)), Input!N1:N100)

I have a function called MatchNames in my VBA, but I can't work out what it needs to return in order to work. I've tried returning an array of boolean, integer (set to 0 or 1), I've tried transposing the results before returning them, but nothing is working. I've debugged through the MatchNames function, and it does return something "useful" (i.e. an array of booleans), so it's not that the function is bombing out part way through, but I get #VALUE! when I try to use it in a SUMPRODUCT.

This is my VBA function...

Public Function MatchNames(ByVal rng1 As Range, rng2 As Range) As Boolean()
    Dim blnOut() As Boolean
    Dim k As Long

    ReDim blnOut(rng1.Rows.Count - 1)

    For k = 1 To rng1.Rows.Count
        If rng1.Cells(k, 1).Value <> "" Then
            If rng1.Cells(k, 1).Value <> rng2.Cells(k, 1).Value Then
                blnOut(k - 1) = True
            End If
        End If
    Next

    MatchNames = blnOut
End Function

Upvotes: 1

Views: 2718

Answers (1)

barry houdini
barry houdini

Reputation: 46331

I think your MatchNames array needs to be transposed as you suggest (because it appears to be returning the equivalent of a row of values - which doesn't work with the other columns of values in SUMPRODUCT).

I don't know how you'd transpose that in VBA but if you can't do that then transpose in SUMPRODUCT like

=SUMPRODUCT(--(input!L1:L100="Y"),--TRANSPOSE(MatchNames(input!A1:A100,input!K1:K100)), input!N1:N100)

but use of TRANSPOSE means that formula now needs to be "array-entered" with CTRL+SHIFT+ENTER

...or you can use MMULT which will multiply a 1x100 range by a 100x1, i.e.

=MMULT(MatchNames(input!A1:A100,input!K1:K100)+0,input!N1:N100*(input!L1:L100="Y"))

for that latter formula to work the sum range - input!N1:N100 - must be all numeric

Upvotes: 3

Related Questions