Insert variables into SUMIFS Formula

Good day everybody,

Even though I've tried many different syntax, I don't get to work the following code:
I define Year and Year1 as variables, where year is 2017 and Year1 is 17
Year= ActiveSheet.Range("C3")
Year1=ActiveSheet.Range("C4")

Then, there's a part of the code where I need to use a SUMIFS to other workbook already open, the original formula is:
=SUMIFS('[Comisiones 2017.xlsm]Stats17'!$F$3:$F$10000,'[Comisiones 2017.xlsm]Stats17'!$E$3:$E$10000,C2,'[Comisiones 2017.xlsm]Stats17'!$B$3:$B$10000,G2)

So, the only thing I want to do is that "2017" and "17" can be defined into the formula with both variables, since the names of the files and sheets will be changing depending on the current year.

I had this problem before and I solved it by inserting, litterally something like: "SUMIFS('[Comisiones "&Sheets("MXN").Range("C3")&"..." as if it took the variable directly from the workbook, however, this solution only works for me when the formula and the referenced cell are on the same workbook, and now, the reference workbook is different from the workbook I want to set the formula in.

Thank you in advance for any help you can provide to me,

Best Regards
Carlos CasioFinancial Analyst and DeveloperAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
Have you tried as below:
Dim Yr1 As Date
Dim Yr2 As Date
Dim Result As Variant
Yr1 = Format(Date, "yyyy")
Yr2 = Format(Date, "yy")
Result = "=SUMIFS('[Comisiones " & Yr1 & ".xlsm]Stats" & Yr2 & "'!$F$3:$F$10000,'[Comisiones " & Yr1 & ".xlsm]Stats" & Y2 & " '!$E$3:$E$10000,C2,'[Comisiones " & Yr1 & ".xlsm]Stats" & Y2 & "'!$B$3:$B$10000,G2)"""

Open in new window

0
Carlos CasioFinancial Analyst and DeveloperAuthor Commented:
What I understand from that is it will take the date from today and then show it only as "YYYY" or "YY", if so, I would only have a problem: the year number it's not supposed to be taken from the day the macro is run, but from the date you choose before running the macro on an specific cell.

To be clear, the file gets information from many other Excel files, gathers it, calculates some stuff (like this formula), and then paste as values. However, there are monthly databases that can change from time to time so, if today I get to know the database from January 2016 changed, I would like the code to take the 2016 from an specific cell I set, adding the value to the variable and then set the formula.

I think the code you gave me it is very functional but I still have that problem.

Tyvm
0
NorieVBA ExpertCommented:
Tyvm

I'm pretty sure Shums only used Format(Date, "yyyy"), Format(Date, "yy"), Yr1, Yr2 to test things out.

Try replacing them with your variables/ranges etc.
Year= ActiveSheet.Range("C3")
Year1=ActiveSheet.Range("C4")

' note I've used ActiveCell here because you give no indication of where the formula should go.

ActiveCell.Formula ="=SUMIFS('[Comisiones " & Year & ".xlsm]Stats" & Year1 & "'!$F$3:$F$10000,'[Comisiones 2017.xlsm]Stats" & Year1 & "'!$E$3:$E$10000,C2,'[Comisiones " & Year & ".xlsm]Stats" & Year1 & "'!$B$3:$B$10000,G2)"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Carlos CasioFinancial Analyst and DeveloperAuthor Commented:
Thanks for the help,

I already tried this, and it works, it writes the value filled in the variable. However, variables 'year' and 'year1' are not getting the values I want.

Here is what I have:
year= Sheets("Macros").Range("C3")
year1=Sheets("Variables").Range("O1")
Msgbox year
Msgbox year1

Open in new window


And then the values are:
Year = 09/07/1905
Year1=16/01/1900

I already checked there are values on those 2 cells and there are, so I don't know what's happening,

Thanks!
0
Carlos CasioFinancial Analyst and DeveloperAuthor Commented:
Ok, there is an update, I'm sorry, before year and year1 there was a code to dim both as date.

I erase that and then it says:

Compile error:
Argument not optional

Thanks again
0
NorieVBA ExpertCommented:
What exactly is in C2 on the Macros sheet and O1 on the Variables sheet?

Is it dates formatted to appear as you want them in the formula?
0
Carlos CasioFinancial Analyst and DeveloperAuthor Commented:
C3 is the date the user picks from a deployable list, and O1 is the RIGHT(Year,2) function from C3

Thanks
0
Carlos CasioFinancial Analyst and DeveloperAuthor Commented:
Ok so I did:
Dim year as string
dim year1 as string

And it works perfectly, however I really needed to workout on the formula so I could insert the variables, and I thank you for that help

Greetings!
0
ShumsDistinguished Expert - 2017Commented:
Try as below:
Dim Yr1 As Date
Dim Yr2 As Date
Dim Result As Variant
Yr1 = Format(Sheets("Macros").Range("C3"), "yyyy")
Yr2 = Format(Sheets("Variables").Range("O1"), "yy")
Result = "=SUMIFS('[Comisiones " & Yr1 & ".xlsm]Stats" & Yr2 & "'!$F$3:$F$10000,'[Comisiones " & Yr1 & ".xlsm]Stats" & Y2 & " '!$E$3:$E$10000,C2,'[Comisiones " & Yr1 & ".xlsm]Stats" & Y2 & "'!$B$3:$B$10000,G2)"""

Open in new window

0
Carlos CasioFinancial Analyst and DeveloperAuthor Commented:
The final answer would be:
dim year as string
dim year1 as string
year = (whatever reference you give)
year1= (same as year)

Activecell.formula= "=SUMIFS(text"& variable (year/year1)&"more text..." and so on,

Both helped me get this, thanks!
0
ShumsDistinguished Expert - 2017Commented:
Great minds thinks alike :)
Glad we're able to help you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.