Option Explicit
Dim bCOMvba As New BedvitCOM.VBA 'раннее связывание
Function SUMIFS2(rngIn As Range, columnSum As Long, column1 As Long, filtr1 As Variant, Optional column2 As Long, Optional filtr2 As Variant, Optional column3 As Long, Optional filtr3 As Variant, Optional column4 As Long, Optional filtr4 As Variant, Optional column5 As Long, Optional filtr5 As Variant, Optional column6 As Long, Optional filtr6 As Variant, Optional column7 As Long, Optional filtr7 As Variant) As Double
Dim p, arrRes, arrIn, x As Long, c As Long, res As Double ' i As Long, x As Long,
If column1 > 0 Then c = c + 1: If column2 > 0 Then c = c + 1: If column3 > 0 Then c = c + 1: If column4 > 0 Then c = c + 1: If column5 > 0 Then c = c + 1: If column6 > 0 Then c = c + 1: If column7 > 0 Then c = c + 1
ReDim p(c - 1, 5)
If column1 > 0 Then p(x, 0) = 1: p(x, 1) = 0: p(x, 2) = column1: p(x, 3) = 2: p(x, 4) = filtr1: p(x, 5) = 0: x = x + 1
If column2 > 0 Then p(x, 0) = 1: p(x, 1) = 0: p(x, 2) = column2: p(x, 3) = 2: p(x, 4) = filtr2: p(x, 5) = 0: x = x + 1
If column3 > 0 Then p(x, 0) = 1: p(x, 1) = 0: p(x, 2) = column3: p(x, 3) = 2: p(x, 4) = filtr3: p(x, 5) = 0: x = x + 1
If column4 > 0 Then p(x, 0) = 1: p(x, 1) = 0: p(x, 2) = column4: p(x, 3) = 2: p(x, 4) = filtr4: p(x, 5) = 0: x = x + 1
If column5 > 0 Then p(x, 0) = 1: p(x, 1) = 0: p(x, 2) = column5: p(x, 3) = 2: p(x, 4) = filtr5: p(x, 5) = 0: x = x + 1
If column6 > 0 Then p(x, 0) = 1: p(x, 1) = 0: p(x, 2) = column6: p(x, 3) = 2: p(x, 4) = filtr6: p(x, 5) = 0: x = x + 1
If column7 > 0 Then p(x, 0) = 1: p(x, 1) = 0: p(x, 2) = column7: p(x, 3) = 2: p(x, 4) = filtr7: p(x, 5) = 0: x = x + 1
arrIn = rngIn
bCOMvba.ArrayFilterV arrIn, p, 1, arrRes 'применяем фильтр
For x = 1 To UBound(arrRes)
SUMIFS2 = SUMIFS2 + arrIn(arrRes(x), columnSum)
Next
End Function
|