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

 

[teylyn]

because I don't want to print to the worksheet in order to obtain a range for a Worksheet.function.Sumif

I don't understand that reasoning. Can you explain in words what you would like to calculate? If it can be done with Sumif in a worksheet, it can be done with Sumif in VBA.

 

[Silverjman]

I'm under the impression that Sumif demands a Range type, whereas I have an array that is just sitting in VBA. I don't know how to convert an array to a range other than by printing it to the worksheet and then sucking that back up to VBA becasue I am trying to optimize for speed.

I thought a If Sum loop would be fairly simple...

 

[Silverjman]

*and then sucking that back up to VBA which I am trying to avoid because

 

[DanteAmor]

You can put a sample of your data and the expected results.

Use XL2BB tool

 

[teylyn]

Your code resets the variable to 0 when just ONE item in the array does not meet the condition. You may want to change that to do just nothing, so the variable retains the value it has and on the next loop iteration the next value can be added to the previous one.

If you want a different result, don't set the variable to zero.

Also, indent your code properly, so it's easier to find where things are at.

Does that make sense?

 

[Legacy 456129]

Certainly found that WorksheetFunction.Sum(1DArray) did work.

No, looking at a code snip of something I'm running WorksheetFunction.Sum(Application.Index(2DArray, , 2)) works for me too.

Some go where none have tried to go before.

 

[Fluff]

The OP is doing a SumIF, not sum

 

[Silverjman]

Cool, OK so I don't know if it picked up my named ranges so I've color coded them. I made a separate sample workbook and the code works correctly until quarter 11 (red).

Option Explicit

Sub SumifONarray()
Dim arrQuarters, arrNumber_of_Assets As Variant

Dim I As Long, J As Long

arrNumber_of_Assets = Range("Costs_Number_of_Assets")
arrQuarters = Range("Quarters_1to40")

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 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
End Sub

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
7	This row is what I am try to find with "arrIf_Max_Recovery_Amount_Sum" in the VBA code				496,129	496,129	496,129	496,129	496,129	496,129	496,129	496,129	496,129	422,803	376,567	376,567	376,567	329,622	329,622	182,375	182,375	90,437	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-	-
8					Quarters_1to40
9	Costs_Number_of_Assets	_Row10_Resolution_Physical_Possession_Expenses_To_Quarter			1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34	35	36	37	38	39	40
10	1	9		_Row10_Resolution__Max_Recovery_Grid	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327	73,327
11	2	18			42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919	42,919
12	3	15			50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174	50,174
13	4	13			46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945	46,945
14	5	17			47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951	47,951
15	6	18			47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518	47,518
16	7	15			46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236
17	8	10			46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236	46,236
18	9	15			50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837	50,837
19	10	17			43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986	43,986
Sheet1

Cell Formulas
Range	Formula
E7:AR7	E7	=SUMIFS(E$10:E$19,_Row10_Resolution_Physical_Possession_Expenses_To_Quarter,">="&E$9)

 

[Silverjman]

ALSO: just uploaded the file here easyupload.io