SumIf and SumIfs Help

[gmaxey]

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.

[Paul_Hossler]

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

[Aflatoon]

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.

[p45cal]

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)]

.