Sumifs VBA - Multiple criterias

[Bumrah]

Folks,

Need help to understand why introducing a new criteria in a code which basically executes sumifs function based on criterias defined in a config file(Sum range, Crit range & Crit is defined in a master excel), doesnt work?

With existing code works perfectly for a single criteria but if introduce a new criteria (CRT_rng1) and additional range (new column in config file) add them to the code in DATA_VAL, it returns 0 instead of expected results.

Sample structure of config file is provided below, basically I would like to introduce additional sumifs criteria below file by adding two columns.

File Path	Sheet Name	Row Field Name	ColField Number	Sum_COLUMN	DATA	HEADER_ROW
C:\Users\Bumrah\Documents\Test.xlsx	Sheet1	AAA	8	24	ZZZ	3

<tbody>
</tbody>

Existing code which works fine with single criteria for sumifs is as below:

[FONT=Arial]Sub SUMIFS_CONSOL_TEST()[/FONT]
[FONT=Arial]Dim THIS_FILE As Workbook[/FONT]
[FONT=Arial]Set THIS_FILE = ActiveWorkbook[/FONT]
[FONT=Arial]config = Range("CONFIG")[/FONT]
[FONT=Arial]For i = 2 To UBound(config)[/FONT]
[FONT=Arial]    Dim SOURCE_FILE As Workbook[/FONT]
[FONT=Arial]    Set SOURCE_FILE = Workbooks.Open(config(i, 1), UpdateLinks:=False, ReadOnly:=True)[/FONT]
[FONT=Arial]    SOURCE_FILE.Activate[/FONT]
[FONT=Arial]    SOURCE_FILE.Sheets(config(i, 2)).Activate[/FONT]
[FONT=Arial]    On Error Resume Next[/FONT]
[FONT=Arial]    DATA_ROW = Cells.Find(config(i, 3)).Row[/FONT]

[FONT=Arial]    If Err.Number <> 0 Then[/FONT]
[FONT=Arial]        GoTo Error_HAND:[/FONT]
[FONT=Arial]    Else[/FONT]
[FONT=Arial]        Err.Clear[/FONT]
[FONT=Arial]    End If[/FONT]

[FONT=Arial]    DATA_COL = config(i, 4)[/FONT]
[FONT=Arial]    HEAD_ROW = config(i, 6)[/FONT]

[FONT=Arial]    Dim DATA_VAL As Double[/FONT]

[FONT=Arial]   Dim SUM_RNG As Range[/FONT]
[FONT=Arial]   Dim CRT_rng As Range[/FONT]
[FONT=Arial]   Set SUM_RNG = Range("A:A").Offset(0, config(i, 5) - 1)[/FONT]
[FONT=Arial]   Set CRT_rng = Range("A:A").Offset(0, config(i, 4) - 1)[/FONT]

[FONT=Arial]    DATA_VAL = Application.WorksheetFunction.[/FONT]<wbr style="font-family: Arial, Helvetica, sans-serif; font-size: small;">[FONT=Arial]SumIfs(SUM_RNG, CRT_rng, config(i, 3))[/FONT]


[FONT=Arial]    THIS_FILE.Activate[/FONT]

[FONT=Arial]    Sheet2.Select[/FONT]
[FONT=Arial]          DEST_ROW = Range("A1048576").End(xlUp).[/FONT]<wbr style="font-family: Arial, Helvetica, sans-serif; font-size: small;">[FONT=Arial]Row + 1[/FONT]
[FONT=Arial]          Range("A" & DEST_ROW).Value = config(i, 2)[/FONT]
[FONT=Arial]          Range("B" & DEST_ROW).Value = config(i, 3)[/FONT]
[FONT=Arial]          Range("C" & DEST_ROW).Value = config(i, 6)[/FONT]
[FONT=Arial]          Range("D" & DEST_ROW).Value = DATA_VAL[/FONT]
[FONT=Arial]          Sheet1.Select[/FONT]
[FONT=Arial]         Sheet1.Range("G" & i) = "SUCCESS"[/FONT]
[FONT=Arial]Error_HAND:[/FONT]
[FONT=Arial]    If Sheet1.Range("G" & i) = "" Then Sheet1.Range("G" & i) = "FAILED"[/FONT]
[FONT=Arial]    SOURCE_FILE.Close[/FONT]
[FONT=Arial]    Set SOURCE_FILE = Nothing[/FONT]
[FONT=Arial]    THIS_FILE.Activate[/FONT]

[FONT=Arial]Next i[/FONT]



[FONT=Arial]End Sub

I tried testing sumifs formula in my test file and it returned results which is not 0, hence I am bit baffled. Any help in pointing me where I am going wrong would be highly appreciated, thanks in advance.

Cheers!

[/FONT]

 

[Bumrah]

I think I found why this doesnt work, coz with multiple criterias I must use "" for criteria1 & criteria2, which actually I am defining in my config file [(i,3) & (i,7)].

e.g. DATA_VAL = Application.WorksheetFunction.<wbr style="color: rgb(51, 51, 51); font-family: Arial, Helvetica, sans-serif; font-size: small;">SumIfs(SUM_RNG, CRT_rng, config(i, 3), CRT_rng1, config(i,7))

Based on what I gather from googling, is using Evaluate might be a workaround, unable to figure out the right syntax though, anyways will keep at it. Would be grateful if someone could help with a better solution.

TIA.