Office 365 - EXCEL VBA SUMIFS with Variable Date

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")

Open in new window


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)

Open in new window


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))

Open in new window


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
LVL 30
Randy DownsOWNERAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
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".

Try this formula instead:

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

Thanks
Rob H
0
 
Rob HensonFinance AnalystCommented:
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
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Randy DownsOWNERAuthor Commented:
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
 
Randy DownsOWNERAuthor Commented:
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
 
Randy DownsOWNERAuthor Commented:
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))

Open in new window

0
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.

All Courses

From novice to tech pro — start learning today.