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(Activ eSheet.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)].