none
Sumifs vba code with multiple columns RRS feed

  • Question

  • Hi All

    How to make sumifs in vba code  with dynamic range  with 2 columns as criteria  as below as summary for unique invoices and origin

    Invoices
    Origin
    amount
    570708A
    BD
    409.21
    570708A
    BD
    519.2
    570708A
    BD
    48.06
    222222b

    IN
    127.5
    333333

    BD
    77.1
    251566

    BD
    73.44
    570708A
    MU
    85.8
    333333

    MU
    31.46
    222222b
    IN
    34.95
    570708A
    BD
    156
    4444444

    IN
    125.28
    570708A
    BD
    1072
    570708A
    IN
    63.9
    4444444
    IN
    286.58


    Tuesday, February 9, 2016 6:57 AM

Answers

  • The code below relies on your data being set up as per the screen capture. An alternative method of setting up dynamic named ranges could also be used but requires more initial setting up.

    You might want to use a different method of entering the criteria.

    Sub Macro1()
        Dim wsSht As Worksheet
        Dim lngLastRow As Long
        Dim rngInvoices As Range
        Dim rngOrigin As Range
        Dim rngAmount As Range
        Dim strCriteria1 As String
        Dim strCriteria2 As String
        Dim dblTotal As Double
       
        Set wsSht = Worksheets("Sheet1")     'Edit "Sheet1" to your worksheet name
        lngLastRow = LastRowOrCol(wsSht, True, wsSht.Columns("A:C"))   'Finds the last used row in columns A:C
       
        With wsSht
            Set rngInvoices = .Range(.Cells(2, "A"), .Cells(lngLastRow, "A"))
            Set rngOrigin = .Range(.Cells(2, "B"), .Cells(lngLastRow, "B"))
            Set rngAmount = .Range(.Cells(2, "C"), .Cells(lngLastRow, "C"))
        End With
       
           
        strCriteria1 = Application.InputBox(Prompt:="Enter first criteria.", Type:=2)
       
        strCriteria2 = Application.InputBox(Prompt:="Enter first criteria.", Type:=2)
       
        strCriteria1 = "=" & strCriteria1
        strCriteria2 = "=" & strCriteria2
       
        dblTotal = WorksheetFunction.SumIfs(rngAmount, _
                    rngInvoices, strCriteria1, _
                    rngOrigin, strCriteria2)
           
        MsgBox dblTotal

    End Sub


    Function LastRowOrCol(ws As Worksheet, bolRowCol As Boolean, Optional rng As Range) As Long
        'Finds the last used row or column in a worksheet
        'First parameter is Worksheet
        'Second parameter is True for Last Row or False for last Column
        'Third parameter is optional. Use to find the last row or column in a specific range
       
        Dim lngRowCol As Long
        Dim rngToFind As Range
       
        If rng Is Nothing Then
            Set rng = ws.Cells
        End If
       
        If bolRowCol Then
            lngRowCol = xlByRows
        Else
            lngRowCol = xlByColumns
        End If
       
        With ws
            Set rngToFind = rng.Find(What:="*", _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=lngRowCol, _
                    SearchDirection:=xlPrevious, _
                    MatchCase:=False)
        End With
       
        If Not rngToFind Is Nothing Then
            If bolRowCol Then
                LastRowOrCol = rngToFind.Row
            Else
                LastRowOrCol = rngToFind.Column
            End If
        End If
       
    End Function


    Regards, OssieMac

    • Proposed as answer by ryguy72 Monday, February 15, 2016 12:15 AM
    • Marked as answer by Jean_7 Sunday, July 23, 2017 5:38 PM
    Tuesday, February 9, 2016 10:46 AM