Consulting

Results 1 to 4 of 4

Thread: SumIf and SumIfs Help

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1
    Microsoft Word MVP 2003-2009 VBAX Guru gmaxey's Avatar
    Joined
    Sep 2005
    Posts
    3,400
    Location

    SumIf and SumIfs Help

    I am one step above and Excel nub. Still I am trying sum the values in one column (E) IF the value in a second column (C) is ".pdf" OR ".wbk" (and ideally more that two "OR" conditions:

    Sub TestingFunctions()
      'SumIf - first arg is the range where condition could exist, second arg is the condition, third arg is the range containing values to sum.
      'I have a workbook with a column listing file extensions (Column C) and another listing file size (Column C).  I can sum the file size for all files with the single extension .wbk as follows:
      MsgBox Application.WorksheetFunction.SumIf(ActiveSheet.Range("C:C"), ".wbk", ActiveSheet.Range("E:E"))
      'I can combine the two such statements to sum the size of all file with extensions .pdf or .wbk as follows:
      MsgBox Application.WorksheetFunction.Sum(Application.WorksheetFunction.SumIf(ActiveSheet.Range("C:C"), ".pdf", ActiveSheet.Range("E:E")) + Application.WorksheetFunction.SumIf(ActiveSheet.Range("C:C"), ".wbk", ActiveSheet.Range("E:E")))
      'Is there a better way?  Is there a way to use an OR?  E.g., SumIf(C:C, ".pdf" OR ".wbk" OR ".docm", E:E)
      'I have tried using SumIfs as follows:
       MsgBox Application.WorksheetFunction.SumIfs(ActiveSheet.Range("E:E"), ActiveSheet.Range("C:C"), ".wbk", ActiveSheet.Range("C:C"), ".pdf")
      'but this returns 0 (as if it performs as an AND function).
    End Sub
    Thank you.
    Greg

    Visit my website: http://gregmaxey.com

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,796
    Location
    I think SUMIFS does do an AND. Look at the attachment

    This would be the way I'd do it unless I made a user defined function for flexibility

    Option Explicit
    
    
    Sub GM()
        Dim rExt As Range, rSize As Range
        Dim dTotal As Double
    
    
        Set rExt = ActiveSheet.Columns(3)
        Set rSize = ActiveSheet.Columns(5)
    
    
        With Application.WorksheetFunction
            dTotal = .SumIf(rExt, "docx", rSize) + .SumIf(rExt, "docm", rSize) + .SumIf(rExt, "xlsx", rSize)
            
            MsgBox "docx+docm+xlsx = " & Format(dTotal, "#,##0")
            
        End With
    
    
    End Sub
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,783
    Location
    You can do it the same way you would in a formula, with SUM, SUMIFS and an array:

    MsgBox Application.Sum(Application.SumIfs(ActiveSheet.Range("E:E"), ActiveSheet.Range("C:C"), Array(".wbk", ".pdf")))
    Note that you have to use Application.Sumifs and not Worksheetfunction.Sumifs.
    Be as you wish to seem

  4. #4
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,931
    These both work here (for those with versions of Excel before 2007):
      MsgBox Application.Sum(Application.SumIf(Range("C:C"), [{".wbk",".pdf"}], Range("E:E")))
      MsgBox Application.Sum(Application.SumIf(Range("C:C"), Array(".wbk", ".pdf"), Range("E:E")))
    This works in a cell:
    =SUM((C:C={".wbk",".pdf"})*E:E)
    but I can't translate it to vba - except by cheating:
      MsgBox [SUM((C:C={".wbk",".pdf"})*E:E)]
    .
    Last edited by p45cal; 09-27-2024 at 04:05 AM.
    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.

Posting Permissions

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