CODEWITHSUNDEEP
vbaexcel
Ryflex
Ryflex

Reputation: 5779

Excel VBA doing a sum based on a matched cells row via formula

I'm trying to sum a set of columns based on matched cells via formula...

Basically, to get the row I am using: =MATCH(A3,SHEETNAME!$A$1:$A$100,0)

So what I attempted was: 

=IF(MATCH(A3,SHEETNAME!$A$1:$A$100,0), SUM(SHEETNAME!L&MATCH(A3,SHEETNAME!$A$1:$A$100,0)&:U&MATCH(A3,SHEETNAME!$A$1:$A$100,0)), "")

I'm trying to make it sum the columns L:U in the matched row... what am I doing incorrectly?

Upvotes: 0

Views: 70

Answers (1)

user4039065
user4039065

Reputation:

As a worksheet formula:

=IF(COUNTIF(SHEETNAME!$A$1:$A$100, A3), SUM(INDEX(SHEETNAME!L1:U100, MATCH(A3, SHEETNAME!$A$1:$A$100, 0), )), "")

As VBA code in UDF function:

Function udf_Sum_Matching_Row(rVAL As Range)
    Dim rw As Long, dTTL As Double
    With Sheets("SHEETNAME")
        If CBool(Application.CountIf(.Columns(1), rVAL)) Then
             rw = Application.Match(rVAL, .Columns(1), 0)
             dTTL = Application.Sum(.Cells(rw, 12).Resize(1, 10))
        End If
    End With
    udf_Sum_Matching_Row = dTTL
End Function

Syntax on a worksheet as per your example:

=udf_Sum_Matching_Row(A3)

Upvotes: 2

Related Questions