Consulting

Results 1 to 6 of 6

Thread: Using VBA SUMIF with multiple conditions

  1. #1
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location

    Using VBA SUMIF with multiple conditions

    I'm using SUMIF to get the gross for each person and list them separately, but I want to extend it to only Sum the totals over $100.00 on Column B to put into Column F, and to only count the amounts also over $100 for Column H.

    The VBA portion that Sums the total for Column F is :

        Range("F2:F" & Endrow).Formula = "=SUMIF(A:A,E2,B:B)"
    How can I re-write it so that it only Sums & counts the entries on Column B, and to fill in Column I & J ?
    See the Attached .xls sheet
    Attached Files Attached Files

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    you can use "sumifs".

  3. #3
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    & For those interested, this works

    =SUMIFS(B:B,A:A,E2,B:B, ">100.00")

  4. #4
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    alternative method using array instead of formula


    Option Explicit
    
    Sub test()
        Dim tbl As Range, v()
        Dim dic As Object
        Dim i As Long, n As Long, s
        Dim m As Long
        
        Set tbl = Cells(1).CurrentRegion
        ReDim v(1 To tbl.Count - 1, 1 To 7)
        
        Set dic = CreateObject("scripting.dictionary")
        
        For i = 2 To tbl.Rows.Count
            s = tbl(i, 1)
            If Not dic.exists(s) Then
                dic(s) = dic.Count + 1
                v(dic.Count, 1) = s
            End If
            n = dic(s)
            v(n, 2) = v(n, 2) + tbl(i, 2)      'gross
            v(n, 4) = v(n, 4) + 1              'count
            If tbl(i, 2) > 100 Then
                v(n, 5) = v(n, 5) + 1           'Count Over $100.00
                Select Case Year(tbl(i, 3))
                    Case 2015                    '2015Gross > $100.00
                        v(n, 6) = v(n, 6) + tbl(i, 2)
                    Case 2016                    '2016Gross > $100.00
                       v(n, 7) = v(n, 7) + tbl(i, 2)
                End Select
            End If
        Next
        
        m = dic.Count + 2
        
        For i = 1 To m
            v(m, 2) = v(m, 2) + v(i, 2)
            v(m, 4) = v(m, 4) + v(i, 4)
            v(m, 5) = v(m, 5) + v(i, 5)
            v(m, 6) = v(m, 6) + v(i, 6)
            v(m, 7) = v(m, 7) + v(i, 7)
        Next
        
        Columns("e:k").Resize(Rows.Count - 1).Offset(1).ClearContents
        Range("e2").Resize(m, 7).Value = v
        
    End Sub

  5. #5
    VBAX Mentor
    Joined
    Jan 2008
    Posts
    384
    Location
    mana: Thanks !

    Didn't see your post until now. Not sure what happened.

  6. #6
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    I'm sorry, there was a mistake in my code.
    Please correct.


    wrong: For i = 1 To m
    right: For i = 1 To dic.Count

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •