Go Premium for a chance to win a PS4. Enter to Win

x
Solved

Office 365 - EXCEL VBA SUMIFS with Variable Date

Posted on 2015-02-12
Medium Priority
205 Views
I can sum spreadsheet values based on the dates entering them manually like this. Dates are in column B and dollar values in column N.
``````SUMIFS(N4:N144,B4:B144,">=2015-02-09",B4:B144,"<=2015-02-14")
``````

I have issues trying it like with date variables: week7_start, week7_end. I don't get anything summed doing it this way.
``````SUMIFS(N4:N144,B4:B144,">="&week7_start,B4:B144,"<="&week7_end)
``````

I have tried using Clng & CDate but it doesn't seem to matter. What am I missing?
``````SUMIFS(N4:N144,B4:B144,">="&Clng(week7_start),B4:B144,"<="&Clng(week7_end))
``````

Here's what the output looks like on my spreadheet.

Week            Start Date      End Date
Week 7       \$(93.31)      8-Feb      14-Feb      Manual dates

Week 7       \$-         8-Feb      14-Feb      Variable dates
0
Question by:Randy Downs
• 3
• 3

LVL 34

Expert Comment

ID: 40606144
How are you defining "week7_start" or "Clng(week7_start)"?

I don't recognise those as standard Excel functions.

Do you have a User Defined Function to generate the dates?

Thanks
Rob H
0

LVL 34

Accepted Solution

Rob Henson earned 2000 total points
ID: 40606161
With the sample supplied, and some assumptions:

Week            Start Date      End Date
Week 7       \$(93.31)      8-Feb      14-Feb

Assumptions
1) Week = Col Q
2) Value = Col R
3) Start = Col S
4) End = Col T
5) Start and End dates are true dates formatted to show as "d-mmm".

=SUMIFS(N1:N144,B1:B144,">"&S2,B1:B144,"<="&T2)

Thanks
Rob H
0

LVL 34

Assisted Solution

Rob Henson earned 2000 total points
ID: 40606189
Just noticed the mention of VBA, are you trying to insert the formula into the spreadsheet using VBA or are you trying to calculate using VBA and then put the value in the cell?

If inserting the formula/value from a formula you will have to use combinations of double quotes and & to split the formula into hard coded text and variables.

ActiveCell.Formula = "=SUMIFS(N4:N144,B4:B144,"&">="&" week7_start & ",B4:B144," & "<=" & week7_end &")"

You might need to tweak with double double quotes to allow for the double quotes around the >= parts.

Alternatively the straight VBA conversion of the formula I suggested earlier is:

ActiveCell.FormulaR1C1 = _
"=SUMIFS(R[-1]C[-4]:R[142]C[-4],R[-1]C[-16]:R[142]C[-16],"">""&RC[1],R[-1]C[-16]:R[142]C[-16],""<=""&RC[2])"
0

LVL 30

Author Comment

ID: 40606318
I am defining variables on a separate worksheet and giving them names to make them easily recognizable in my formulas. The variables week7_start week7_end are formatted as dates like:  8-Feb      14-Feb. Perhaps the issue is not displaying the year.

The idea is to update my variables once a quarter and then all my formulas will follow suit. There will be 14 weeks when I am done.

It's really not VBA at this point. Sorry I misled you.
0

LVL 30

Author Comment

ID: 40606325
I notice that when I format the dates as month/day/year rather than just day/month I end up with 2014 rather than the expected 2015. I have re-used an old spreadsheet so maybe that's the issue.
0

LVL 30

Author Comment

ID: 40606336
Now the formulas work. They were apparently trying to compare last year's date. Thanks for your suggestions to get me started in the right direction.

``````SUMIFS(N4:N144,B4:B144,">="&Clng(week7_start),B4:B144,"<="&Clng(week7_end))
``````
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
New style of hardware planning for Microsoft Exchange server.
how to add IIS SMTP to handle application/Scanner relays into office 365.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
Suggested Courses
Course of the Month9 days, 20 hours left to enroll