SUMIFS in VBA

[TG2812]

Hi, can anyone explain me how to translate this formula excel into VBA?

=SUMIFS($H:$H,$A:$A,$A2,$B:$B,$B2,$D:$D,$D2,$E:$E,$E2)

Thanks in advance for your help.

 

[Trebor76]

Re: SUMIFS in VBA Help needed please

Hi TG2812,

This will put the value of the formula to a variable called dblMyVal:

Option Explicit
Sub Macro1()

    Dim dblMyVal As Double
    
    dblMyVal = WorksheetFunction.SumIfs(Range("$H:$H"), Range("$A:$A"), Range("$A2"), Range("$B:$B"), Range("$B2"), Range("$D:$D"), Range("$D2"), Range("$E:$E"), Range("$E2"))
    
    MsgBox dblMyVal

End Sub

HTH

Robert

 

[TG2812]

Re: SUMIFS in VBA Help needed please

Thank you Robert. Works like a charm. Just a small adjustment.

I have date in column E. How can I ensure the formula sums up data from the same month and year?

 

[Trebor76]

Re: SUMIFS in VBA Help needed please

Along with your other criteria, this will sum all the dates in Col. E within the range 1-Dec-2018 to 31-Dec-2018:

=SUMIFS($H:$H,$A:$A,$A2,$B:$B,$B2,$D:$D,$D2,$E:$E,">=2018/12/1",$E:$E,"<=2018/12/31")

HTH

Robert

 

[TG2812]

Re: SUMIFS in VBA Help needed please

Hi,

It still does not work:

dblMyVal = WorksheetFunction.SumIfs(Range("$H:$H"), Range("$A:$A"), Range("$A2"), Range("$B:$B"), Range("$B2"), Range("$D:$D"), Range("$D2"), Month(Range("$E:$E")), "=" & Month(Range("$E2")))

 

[Trebor76]

Re: SUMIFS in VBA Help needed please

I'm not sure if you can do a SUMIF on month like that Someone on the forum will a better idea I'm sure

 

[TG2812]

Re: SUMIFS in VBA Help needed please

Does anyone have an idea on how to solve the matter? Thank you in advance for the support.

 

[Joe4]

Re: SUMIFS in VBA Help needed please

Use SUMPRODUCT instead of SUMIFS if you want to use a month criteria.
See: https://www.mrexcel.com/forum/excel-questions/691766-using-sumproduct-adding-month-criteria.html

 

[TG2812]

Re: SUMIFS in VBA Help needed please

Thanks for the info! Any idea how to do it in VBA?

 

[Joe4]

Re: SUMIFS in VBA Help needed please

Thanks for the info! Any idea how to do it in VBA?

Using the same methods that have already been discussed in this thread (using Worksheetfunction).
It should be the exact same process, just using a different function.
The other thread shows how how to structure the SUMPRODUCT function to work for months.