Consulting

Results 1 to 6 of 6

Thread: I am having a problem with the Application.Sumif function in vba

  1. #1
    VBAX Newbie
    Joined
    Dec 2015
    Posts
    4
    Location

    I am having a problem with the Application.Sumif function in vba

    Hi everyone,

    I have this code:

    Sub Sumif()
    
    Dim Sum As Long
    
    
    Description = UserForm2.comboClothing.Value
    
    
    Sum = Application.SumIf(Sheets("Inventory").Range("F2:F1014"), Description, Sheets("Inventory").Range("D2:D1014"))
    
    MsgBox Sum
    
    
    End Sub
    The value that is returned is always zero. I can't seem to figure out why. Thanks in advance for any help!
    Last edited by Bob Phillips; 12-15-2015 at 04:38 AM. Reason: Added VBA tags

  2. #2
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    1) Are you trying to sum a number from the Inventory Sheet (Range F2:F1014) based on other values in a different range?
    2) and display it in the Userform2.comboClothing.value ?
    3) What is in the Range D2 - D1014 ?
    4) how does it affect the F2:F1014 range?

  3. #3
    VBAX Newbie
    Joined
    Dec 2015
    Posts
    4
    Location
    1.Yes, I am trying to sum the values from the Inventory Sheet based on other values from a different range.
    2.I would like to output the result in a message box. The "description" variable comes from a user form
    3.Range D2-D1014 holds the data that I would like to check with my criteria.
    4.It should not affect the data in the F column

    Basically I need to check to see if the name that the user entered into the userform matches the names in the D column. If it does, then I need to sum the corresponding value in the D column and display the total in the message box. Is this possible?

  4. #4
    VBAX Expert mperrah's Avatar
    Joined
    Mar 2005
    Posts
    744
    Location
    Try something like this:
    Sub vbax54544()
    Dim lr, lrH, x As Long
    Dim nameV As Variant
    Dim nmChoice As String
    Dim sumX As Integer
    
    nmChoice = InputBox("enter the name to sum", "Sum Whom")
    
        lr = Cells(Rows.Count, 6).End(xlUp).Row
            
                sumX = 0
                    
                For i = 2 To lr
                    If Cells(i, 6).Text = nmChoice Then
                        
                        sumX = sumX + Cells(i, 4).Value
                           
                    End If
                Next i
                
            MsgBox "Total for " & nmChoice & " = " & sumX
    End Sub

    This will total everyone separately using column H and I:

    Sub vbax54544_1()
    Dim lr, lrH, x As Long
    Dim nameV As Variant
    Dim nm
    
    nmChoice = InputBox("enter the name to sum", "Sum Whom")
    
        lr = Cells(Rows.Count, 6).End(xlUp).Row
            
        Range("F2:F" & lr).Copy Destination:=Range("H1")
        ActiveSheet.Range("$H$1:$H" & lr).RemoveDuplicates Columns:=1, Header:=xlNo
        
        lrH = Cells(Rows.Count, 8).End(xlUp).Row
            ReDim nameV(1 To lrH)
            For x = 1 To lrH
                nameV(x) = Cells(x, 8).Value
            Next x
            
            For nm = LBound(nameV) To UBound(nameV)
                sumX = 0
                For i = 2 To lr
                    If nameV(nm) = Cells(i, 6) Then
                        
                            sumX = sumX + Cells(i, 4).Value
                           
                    End If
                Next i
                
                Cells(nm, 9).Value = sumX
                
            Next nm
            
    End Sub

  5. #5
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by updown View Post
    1.Yes, I am trying to sum the values from the Inventory Sheet based on other values from a different range.
    2.I would like to output the result in a message box. The "description" variable comes from a user form
    3.Range D2-D1014 holds the data that I would like to check with my criteria.
    4.It should not affect the data in the F column

    Basically I need to check to see if the name that the user entered into the userform matches the names in the D column. If it does, then I need to sum the corresponding value in the D column and display the total in the message box. Is this possible?
    Can you post your workbook, so we can track down the cause?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #6
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    Quote Originally Posted by updown View Post
    matches the names in the D column. If it does, then I need to sum the corresponding value in the D column and…
    Let's get the columns right; here you're saying that the names are in column D AND you want to sum column D!
    Remember, the arguments:
    SUMIF(range, criteria, [sum_range])
    so it's probably going to be your original:
    Sum = Application.SumIf(Sheets("Inventory").Range("F2:F1014"), Description, Sheets("Inventory").Range("D2:D1014"))
    or:
    Sum = Application.SumIf(Sheets("Inventory").Range("DF2:D1014"), Description, Sheets("Inventory").Range("F2:F1014"))
    If one of those doesn't work then as xld says, let's have a look at the workbook.
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

Tags for this Thread

Posting Permissions

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