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.

 

[TG2812]

Re: SUMIFS in VBA Help needed please

Hi Joe4,

Thanks for your reply. I used the thread and the advices provided above. However, I'm still stuck with the below formula as it returns a mismatch error. Any idea why?

rw = Sheets("LogDetails").Range("A" & Rows.Count).End(xlUp).Row


dblMyVal = WorksheetFunction.SumProduct((Range("$H:$H")), (Range("$A:$A") = Range("A" & rw)), (Range("$B:$B") = Range("B" & rw)), (Range("$D:$D") = Range("D" & rw)), (Month(Range("$E:$E")) = Month(Range("E" & rw))), (Year(Range("$E:$E")) = Year(Range("E" & rw))))

 

[Joe4]

Re: SUMIFS in VBA Help needed please

I would recommend building the whole SUMPRODUCT formula in a string, and then using that, i.e.

Dim myFormula as String
myFormula = "SUMPRODUCT((Range(" & Chr(34) & [COLOR=#333333]"$H:$H" & Chr(34) & ...
dblMyVal = WorksheetFunction.Sumproduct(myFormula)
[/COLOR]

One of the advantages to that is if it doesn't work out, you can use a Message Box to see what the value of "myFormula" is to make sure that you have it written correctly.

Note that one challenge you have is differentiating the quotes used as text indicators from literal quotes that you want to show up in your formula. You can use mulitple quotes in a row, i.e. """", but I find that gets rather confusing. I prefer to use Chr(34), which is the ASCII code for quotes, when I want literal quotes in my formula.

 

[TG2812]

Re: SUMIFS in VBA Help needed please

Thanks for your help.

Here is the formula I came up with. However when I embed this into worksheetfunction, it returns an error. Can you please correct the formula if needs be cause I really do not know what else to do..?
Sorry I'm a beginner in VBA..

Thank you in advance Joe4.

myFormula = "SUMPRODUCT((Range(" & Chr(34) & "$H:$H" & Chr(34) & ")" & "*" & "(Range(" & Chr(34) & "$A:$A" & Chr(34) & ")" & " = " & Range("A" & rw) & ")*" & "(Range(" & Chr(34) & "$B:$B" & Chr(34) & ")" & " = " & Range("B" & rw) & ")*" & "(Range(" & Chr(34) & "$D:$D" & Chr(34) & ")" & " = " & Range("D" & rw) & ")*" & "(Month(Range(" & Chr(34) & "$E:$E" & Chr(34) & ")" & " = " & Month(Range("E" & rw)) & "))*" & "(Year(Range(" & Chr(34) & "$E:$E" & Chr(34) & ")" & " = " & Year(Range("E" & rw)) & "))"


MsgBox myFormula

 

[Joe4]

Re: SUMIFS in VBA Help needed please

That formula does not return a valid SUMPRODUCT formula.

Here is how I would approach it. Get the SUMPRODUCT formula working in a normal spreadsheet formula first. Once you get it working, note the formula.
Now, build your "myFormula" string in VBA to exactly match the inner part of the formula. Note it must match exactly, and you should be able to to do that comparing the working formula you built in Excel and your myFormula string (through a message box).

Also, I messed up one part. Since the line of VBA code we ultimately want to use is:

[COLOR=#333333]dblMyVal = WorksheetFunction.Sumproduct(myFormula)[/COLOR]

we don't actually want the =SUMPRODUCT( part as part of the myFormula string (that would be redundant). We just want what is in between the first and last parentheses of our SUMPRODUCT formula.

 

[TG2812]

Re: SUMIFS in VBA Help needed please

I tried your approach but it keeps failing.
Point #1: Here is the formula i entered in a cell without using VBA. It worked.

=SUMPRODUCT(($H$2:$H$4)*($A$2:$A$4=A2)*($B$2:$B$4=B2)*($D$2:$D$4=D2)*(MONTH($E$2:$E$4)=MONTH(E2))*(YEAR($E$2:$E$4)=YEAR(E2)))

Point#2: I translated the above function into VBA . I did the comparison and what appeared in the message box is the same than above. I do not understand what I did wrong.

dblMyVal = WorksheetFunction.SumProduct((Range(" & Chr(34) & "$H:$H" & Chr(34) & ")" & "*" & "(Range(" & Chr(34) & "$A:$A" & Chr(34) & ")" & " = " & Range("A" & rw) & ")*" & "(Range(" & Chr(34) & "$B:$B" & Chr(34) & ")" & " = " & Range("B" & rw) & ")*" & "(Range(" & Chr(34) & "$D:$D" & Chr(34) & ")" & " = " & Range("D" & rw) & ")*" & "(Month(Range(" & Chr(34) & "$E:$E" & Chr(34) & ")" & " = " & Month(Range("E" & rw)) & "))*" & "(Year(Range(" & Chr(34) & "$E:$E" & Chr(34) & ")" & " = " & Year(Range("E" & rw))))

Any improvement suggestions are welcome
Thank you in advance.

 

[Domenic]

Re: SUMIFS in VBA Help needed please

Try using the Evaluate method instead...

dblMyVal = Evaluate("SUMPRODUCT(($H$2:$H$4)*($A$2:$A$4=A2)*($B$2:$B$4=B2)*($D$2:$D$4=D2)*(MONTH($E$2:$E$4)=MONTH(E2))*(YEAR($E$2:$E$4)=YEAR(E2)))")

Note that the string passed to the Evaluate method cannot exceed 255 characters.

 

[TG2812]

Re: SUMIFS in VBA Help needed please

Domenic, thank you very much! However, how can I deal with the variable rw with Evaluate?

 

[Domenic]

Re: SUMIFS in VBA Help needed please

Try...

dblMyVal = Evaluate("SUMPRODUCT(($H$2:$H$4)*($A$2:$A$4=A" & rw & ")*($B$2:$B$4=B" & rw & ")*($D$2:$D$4=D" & rw & ")*(MONTH($E$2:$E$4)=MONTH(E" & rw & "))*(YEAR($E$2:$E$4)=YEAR(E" & rw & ")))")

 

[TG2812]

Re: SUMIFS in VBA Help needed please

After browsing a bit, I was able to modify the formula to include the variable rw. However, dblMyVal is still empty..

dblMyVal = Evaluate("SumProduct(($H:$H)*($A:$A=A" & rw & ")*($B:$B=B" & rw & ")*($D:$D=D" & rw & ")*(Month($E:$E=E" & rw & ")=Month(E" & rw & "))*(Year($E:$E)=Year(E" & rw & ")))")

 

[TG2812]

Re: SUMIFS in VBA Help needed please

Domenic, Joe4, to give you a bit of background, here is my full code;

Dim oldAddress As String
Dim oldValue As String
---------------------------------------------------
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo err
Dim sSheetName As String, dblMyVal As Double
sSheetName = "Main"


If ActiveSheet.name = "Main" Then
Application.EnableEvents = False


Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = Range("A" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = Range("B" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 2).Value = Range("C" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 3).Value = Range("D" & Target.Row).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 4).Value = Cells(2, Target.Column).Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 5).Value = oldValue
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 6).Value = Target.Value
Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 7).Value = Target.Value - oldValue


rw = Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Row


dblMyVal = Evaluate("SumProduct(($H:$H)*($A:$A=A" & rw & ")*($B:$B=B" & rw & ")*($D:$D=D" & rw & ")*(Month($E:$E=E" & rw & ")=Month(E" & rw & "))*(Year($E:$E)=Year(E" & rw & ")))")


Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 8).Value = dblMyVal
Sheets("Tracking").Hyperlinks.Add anchor:=Sheets("Tracking").Range("A" & Rows.Count).End(xlUp).Offset(0, 9), Address:="", SubAddress:="'" & sSheetName & "'!" & oldAddress, TextToDisplay:=oldAddress
End If




err:
Application.EnableEvents = True
End Sub

---------------------------------------------------

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo err
oldValue = Target.Value
oldAddress = Target.Address


err:
End Sub