VBA SumIFs not working whilst SumIF with same arguments does

[pvans267]

Good afternoon,

I hope someone can help me with this. I am trying to build a SumIfs formula in VBA. I have tested this formula in the worksheet by hardcoding it and it works - but when I enter the formula in VBA, I get the error: "Run-time Error '1004': Unable to get the SumIfs property of the WorksheetFunction class.'

My ranges I have also tested individually (using simple for loops to see that I am getting the correct range of values) and if I create a simple SumIf calculation using the same ranges, it works.

My formula is as follows:

vDate1 = Worksheets("Sheet1").Range("A2")        ' value is 24/12/2016

Set rngInputsDivisionColumn = Worksheets("Sheet1").Range("C21:C26")
Set rngInputsYearColumn = Worksheets("Sheet1").Range("D21:D26")
Set rngInputsIncomeColumn = Worksheets("Sheet1").Range("G21:G26")

vProfit = Application.WorksheetFunction.SumIfs(rngInputsIncomeColumn , rngInputsYearColumn , ">=" & (Year(vDate1)-3))
' Further condition of which division will be added once I can get the formula working
' This expression doesn't work

vProfitAlt = Application.WorksheetFunction.SumIf(rngInputsYearColumn , ">=" & (Year(vDate1)-3), rngInputsIncomeColumn)
' This formula does work.  Even though I am using the same ranges and conditions as above

It is certainly possible that I am making an incredibly obvious and foolish mistake... but for the life of me, I can't see where.

Please could someone suggest what could be going wrong.

If it helps, here is the table of values on the sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]Drinks[/TD]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]Drinks[/TD]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]Drinks[/TD]
[TD]2016[/TD]
[TD][/TD]
[TD][/TD]
[TD]10000[/TD]
[/TR]
[TR]
[TD]Tickets[/TD]
[TD]2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]Tickets[/TD]
[TD]2015[/TD]
[TD][/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
</tbody>[/TABLE]

I hope I have been as clear as possible, if not, let me know and i will add as much information as I can

Thank you in advance

Paul

 

[pvans267]

Nevermind, I was being foolish. Due to NULL values in my real data column G, the ranges were of different sizes. Hence the failure.

Still interesting that the SumIf worked even with the different sized ranges... but I guess that is a completely different discussion.

Kind regards,