PDA

View Full Version : VBA SUMIFS Function



CharlieFly
10-19-2017, 04:48 AM
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
10-19-2017, 10:29 AM
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
10-19-2017, 11:31 AM
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 & """)"