VBA SumIfs with OR criteria

[dcuellar]

I would like to get a SumIFs function through VBA that uses an array as criteria. This is what I have so far.

summarySht.range("O15").Value = Application.WorksheetFunction.SumIfs(dataSht.range(janCol & ":" & janCol), _ 
dataSht.range(GetColumnLetter(projectStatusCol) & ":" & GetColumnLetter(projectStatusCol)), projectStatus, _
dataSht.range(GetColumnLetter(programsCol) & ":" & GetColumnLetter(programsCol)), programs, _
dataSht.range(GetColumnLetter(beltDesignationCol) & ":" & GetColumnLetter(beltDesignationCol)), beltDesignation, _
dataSht.range(GetColumnLetter(benefitTypeCol) & ":" & GetColumnLetter(benefitTypeCol)), benefitType)

The variables projectStatus, programs, beltDesignation and benefitType are set up as "Dim projectStatus (0 to 3) As String", for example.

In excel the formula would allow an array within "{}". That will not work in VBA.

Any help with this would be greatly appreciated.

 

[RoryA]

Is the result supposed to be the total of every permutation of the various criteria arrays? (I ask since that wouldn't work as a formula either)

 

[dcuellar]

Also, I had it set up originally to filter the different columns and then sum. My question is, will using a sumif function in VBA make the macro run faster than filter and then summing the column?

 

[dcuellar]

Is the result supposed to be the total of every permutation of the various criteria arrays? (I ask since that wouldn't work as a formula either)

Yes, if I understand you correctly. So, it would be the equivalent of filtering column A for two criteria, Column B for two criteria and then summing Column C.

 

[RoryA]

It is probably simplest to use nested loops to loop through your three criteria arrays and accumulate the results, but I suspect a filter might be faster.