VBA SUMIF on Array

[Silverjman]

OK, so I am wanting to do a sumif on a column on an array because I don't want to print to the worksheet in order to obtain a range for a Worksheet.function.Sumif

The first column total is total of 197,321,164 is correct, the next columns are low and instead of going to quarter 40 the Else kicks in and everything after 8 is 0. The first "To_Quarter" in the array is 9 so with the >= I would think it would go to 9. I tried putting my Next I before the End IF but then it just asks for the For.

Any help would be much appreciated.

Dim MaxRecov_If_result, arr_Row10_Resolution_Physical_Possession_Expenses_To_Quarter, arr_Row10_Resolution__Max_Recovery_Grid As Variant

arr_Row10_Resolution_Physical_Possession_Expenses_To_Quarter = Range("_Row10_Resolution_Physical_Possession_Expenses_To_Quarter")
arr_Row10_Resolution__Max_Recovery_Grid = Range("_Row10_Resolution__Max_Recovery_Grid")

ReDim arrIf_Max_Recovery_Amount_Sum(1 To 1, 1 To UBound(arrQuarters, 2))
        For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
        For I = LBound(arrNumber_of_Assets, 1) To 1 + UBound(arrNumber_of_Assets, 1)
            
   If arr_Row10_Resolution_Physical_Possession_Expenses_To_Quarter(I, 1) >= arrQuarters(1, J) Then
                MaxRecov_If_result = MaxRecov_If_result + arr_Row10_Resolution__Max_Recovery_Grid(I, J)
                    Else: MaxRecov_If_result = 0
                    End If
                Next I
          arrIf_Max_Recovery_Amount_Sum(1, J) = MaxRecov_If_result
     MaxRecov_If_result = 0
       Next J

 

[Kyle123]

Why do you need to? It'll only ever have one set of numbers?

 

[Kyle123]

You'd need something like:

Public Function SumIfMatrix(ByVal data As Variant, ByVal criteraLookup As Variant, ByVal criterias, ByVal operation As Operator) As Double()
   
    Dim x       As Long
    Dim y       As Long
    Dim ret()  As Double
   
    ReDim ret(1 to 1, LBound(data, 2) To UBound(data, 2))
   
    For y = LBound(ret) To UBound(ret)
        For x = LBound(criteraLookup) To UBound(criteraLookup)
            Select Case operation
                Case Operator.GreaterThanOrEqualTo
                    If criteraLookup(x, 1) >= criterias(1, y) Then ret(1, y) = ret(1, y) + data(x, y)
                Case Operator.GreaterThan
                    If criteraLookup(x, 1) > criterias(1, y) Then ret(1, y) = ret(1, y) + data(x, y)
                Case Operator.EqualTo
                    If criteraLookup(x, 1) = criterias(1, y) Then ret(1, y) = ret(1, y) + data(x, y)
                Case Operator.LessThanOrEqualTo
                    If criteraLookup(x, 1) < criterias(1, y) Then ret(1, y) = ret(1, y) + data(x, y)
                Case Operator.LessThan
                    If criteraLookup(x, 1) <= criterias(1, y) Then ret(1, y) = ret(1, y) + data(x, y)
            End Select
        Next x
    Next y

    SumIfMatrix = ret
   
End Function

 

[Silverjman]

Yeah that's what I thought. But I thought that you were implying that I needed to Redim it to 2 dims to use it as a divisor...

I don't get why I am don't get how the SumifMatrix isn't already a (1, J) and then why I can't divide my (I, J) / (1, J) like so

 

[Kyle123]

Why would it be? It’s an array of values, not a range. Arrays are single dimension by default, the only reason your arrays have 2 dimensions is that calling the value property on the range returns a 2d array.

There’s really no reason to return a 2d array, but since you want one, the code above returns it as 2d

 

[Silverjman]

Ahh thanks, didn't realize you were posting different code.

I still can't seem to get it to work. So I guess I need to go back to basics and try and implement it when I have a better understanding of what's happening.

THANKS, starting from a low base but this has been brain expanding!

 

[Silverjman]

OK so I've kind of cheated to get this to work, so again thanks very much for the help!

Basically I couldn't get it to run without an error until I took the array 1X40 output of the function pasted in values to the Worksheet and then sucked it back up into the code. Somehow that converted it into something it would accept. This is superior to what I was doing before which was pasting a 5000X40 grid values running a SUMIF on that grid on the Worksheet and then sucking that line back up into the code

Dim arrSUMIF_Max_Recovery_Amount_Row, arr_CF_Interest, arrPasteValues_Interest, arrSUMIF_Max_Recovery_Amount_Paste As Variant

arr_CF_Interest = Range("_CF_Interest")
arrPasteValues_Interest = Range("All_Assets_PasteValues_Interest")


arrSUMIF_Max_Recovery_Amount_Row = SumIfMatrix(arrResolution__Max_Recovery_Grid, arrPhysical_Possession_Expenses_To_Quarter, arrQuarters, GreaterThanOrEqualTo)
Range("Resolution_SUMIF_Max_Recovery_Amount") = arrSUMIF_Max_Recovery_Amount_Row
arrSUMIF_Max_Recovery_Amount_Paste = Range("Resolution_SUMIF_Max_Recovery_Amount")
        

ReDim arrAll_Assets_Interest_PREP_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                
                arrAll_Assets_Interest_PREP_Grid(I, J) = -arrL_CF_Interest(1, J) * arrResolution__Max_Recovery_Grid(I, J) * (arrQuarters(1, J) <= arrPhysical_Possession_Expenses_To_Quarter(I, 1))
             
             Next J
        Next I

ReDim arrAll_Assets_Interest_Grid(1 To UBound(arrNumber_of_Assets, 1), 1 To UBound(arrQuarters, 2))
        For I = LBound(arrNumber_of_Assets, 1) To UBound(arrNumber_of_Assets, 1)
            For J = LBound(arrQuarters, 2) To UBound(arrQuarters, 2)
                
                        arrAll_Assets_Interest_Grid(I, J) = Min(0, arrAll_Assets_Interest_PREP_Grid(I, J) / WorksheetFunction.Max(1, arrSUMIF_Max_Recovery_Amount_Paste(1, J)))
                  
          Next J
       Next I