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]

How about, something like:

Public Enum Operator
    GreaterThan
    GreaterThanOrEqualTo
    LessThan
    LessThanOrEqualTo
    EqualTo
End Enum

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(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(y) = ret(y) + data(x, y)
                Case Operator.GreaterThan
                    If criteraLookup(x, 1) > criterias(1, y) Then ret(y) = ret(y) + data(x, y)
                Case Operator.EqualTo
                    If criteraLookup(x, 1) = criterias(1, y) Then ret(y) = ret(y) + data(x, y)
                Case Operator.LessThanOrEqualTo
                    If criteraLookup(x, 1) < criterias(1, y) Then ret(y) = ret(y) + data(x, y)
                Case Operator.LessThan
                    If criteraLookup(x, 1) <= criterias(1, y) Then ret(y) = ret(y) + data(x, y)
            End Select
        Next x
    Next y

    SumIfMatrix = ret
   
End Function

Sub test()
    s = SumIfMatrix(Range("E10:AR19").Value, Range("B10:B19").Value, Range("E9:AR9").Value, GreaterThanOrEqualTo)
End Sub

 

[Silverjman]

Looks cool. I am getting a "User-defined type not defined when using Excel" error when it goes to compile.

My interpretation of the what the function wanted: SumIfMatrix(4000X40 grid of target values, column of 4000 various qrts, top row of qrtrs for this grid, ">=")

Not sure if it has something to do with a library or my code.

Excel VBA Macro: User Defined Type Not Defined

I'm getting the above error when trying to execute this macros. I'm pretty new to Macros and coding in general so please forgive the ignorance. Sub DeleteEmptyRows() Dim oTable As Table, oRow As ...

stackoverflow.com

not knowing which to use I tried both of these with the same "type not defined" error result in the first line of the functions code.

SumIfMatrix(arrResolution__Max_Recovery_Grid, arrPhysical_Possession_Expenses_To_Quarter, arrQuarters, ">=")

SumIfMatrix(arrResolution__Max_Recovery_Grid(I, J), arrPhysical_Possession_Expenses_To_Quarter(I, 1), arrQuarters(1, J), ">=")

 

[Kyle123]

You haven’t copied all the code, note the Enum, also you’re not calling the function correctly, look at my example

 

[Silverjman]

Ahh right, OK put the Enum under Option Explicit and used the operator label.

If I don't tag on the .Value I get a "type mismatch" on

ReDim ret(LBound(data, 2) To UBound(data, 2))

I tried changing x y an ret to Variant but then "Can not assign array" if change the SumifMatrix to a Variant as well then I get he type mismatch again. hmmm

and if I do .Value I get an "Object needed" on the line below

arrAll_Assets_Interest_Grid(I, J) = SumIfMatrix(arrResolution__Max_Recovery_Grid(I, J).Value, arrPhysical_Possession_Expenses_To_Quarter(I, 1).Value, arrQuarters(1, J).Value, GreaterThanOrEqualTo)

 

[Kyle123]

Right, you're overthinking/complicating this and making a mess.

1. The function expects arrays, not ranges
2. IT expects full arrays, not individual values - not sure why you'd want to do it
3. The example I posted works on your sample workbook without tweaking - all you'd need to do is replace the Range("E10:AR19").Value with your named ranges (so long as they relate to the same ranges)
4. I think you want this, though I haven't checked what your ranges evaluate to:
   1. SumIfMatrix(arrResolution__Max_Recovery_Grid, arrPhysical_Possession_Expenses_To_Quarter, arrQuarters, GreaterThanOrEqualTo)

 

[Silverjman]

Great thanks Kyle 123 that works wonderfully for anyone that is curious. Really appreciate all your help and patience today!

SumIfMatrix(arrResolution__Max_Recovery_Grid, arrPhysical_Possession_Expenses_To_Quarter, arrQuarters, GreaterThanOrEqualTo)

I don't know why I am getting a subscript out of range error on this line, as they are the same a my old named ranges that worked from the worksheet:

arrAll_Assets_Interest_Grid(I, J) = arrAll_Assets_Interest_PREP_Grid(I, J) / arrSUMIF_Max_Recovery_Amount_Row(1, J)

But I've spent an embarrassingly long time trying to figure it out.

'START Interest
Dim arrSUMIF_Max_Recovery_Amount_Row, arrNPL_CF_Interest, arrPasteValues_Interest As Variant

arrNPL_CF_Interest = Range("NPL_CF_Interest")
arrPasteValues_Interest = Range("All_Assets_PasteValues_Interest")

ReDim arrSUMIF_Max_Recovery_Amount_Row(1 To 1, 1 To UBound(arrQuarters, 2))
arrSUMIF_Max_Recovery_Amount_Row = SumIfMatrix(arrResolution__Max_Recovery_Grid, arrPhysical_Possession_Expenses_To_Quarter, arrQuarters, GreaterThanOrEqualTo)

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) = -arrNPL_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) = arrAll_Assets_Interest_PREP_Grid(I, J) / arrSUMIF_Max_Recovery_Amount_Row(1, J)
              
              
          Next J
       Next I

Range("All_Assets_PasteValues_Interest") = arrAll_Assets_Interest_Grid
'END Interest

 

[Kyle123]

Because arrSUMIF_Max_Recovery_Amount_Row only has one dimension, not two

 

[Silverjman]

Strange, because this works and it is also 1 dimension

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) = -arrNPL_CF_Interest(1, J) * arrResolution__Max_Recovery_Grid(I, J) * (arrQuarters(1, J) <= arrPhysical_Possession_Expenses_To_Quarter(I, 1)) / Max(arrSUMIF_Max_Recovery_Amount(1, J), 1)
          Next J
       Next I
       
Range("All_Assets_PasteValues_Interest") = arrAll_Assets_Interest_Grid

 

[Kyle123]

What does? All the arrays there have 2 dimensions

 

[Silverjman]

OK, yeah predictably you are right, still only 40 numbers, but (1,1) - (1,40) instead of (1) - (40). See screenshot of my Named Range worksheet sumifs, I thought they were the same.

How do I ReDim SumIfMatrix to a two dim array like the below? I thought I had tried every possible combination.