VBA SUMIFS Function

[CharlieFly]

Hey Team,

First post here so hope this is all in the right place and everything!

I'm trying to create a VBA function that looks up a relevant date (specifically year) I.e. "2017" and then takes this information and sums up all the prices of a stock that correspond to this date. This is simple enough in excel but I'm not sure how to create it such that I can easily vary the incoming year or incoming worksheet that I want to refer to with the macro. My code is as follows:

Dim testform As String
Dim rng As String
Dim rngend As String


    Yr = "2017"
If Yr = "2017" Then
    rng = ">01/01/2017"
    rngend = "<=31/12/2017"
End If


    testform = "=SUMIFS(B2:B#,A2:A#,"">01/01/2017"",A2:A#,""<=31/12/2017"")"
    lr = Range("A" & Rows.Count).End(xlUp).Row
    testform = Replace(frmla, "#", lr, 1, -1, 1)
    
    Range("R1").Formula = testform

Essentially this work but it doesn't allow me the ability to change the test range of dates ">01/01/2017" nor am I sure how to insert a relevant sheet into this function (i.e. sheet test!B2:B3)

I hope this is clear what I'm trying to do, let me know if I need to explain further!

Kind Regards,
Charlie

[offthelip]

You cand do this with a simple formula:

=SUMIFS(J3:J14,A3:A14,">="&C2,A3:A14,"<="&D2)

I have assume the start date is in C2 and the end date is in D2
this sums column j with the date in A

[Bob Phillips]

    Yr = "2017"
    rng = ">01/01/" & Yr
    rngend = "<=31/12/" & Yr
     
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("R1").Formula = "=SUMIFS(B2:B" & lr & ",A2:A" & lr & ",""" & rng & """,A2:A" & lr & ",""" & rngend & """)"