Solved

Office 365 - EXCEL VBA SUMIFS with Variable Date

Posted on 2015-02-12
6
159 Views
Last Modified: 2015-02-12
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
0
Comment
Question by:Randy Downs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 33

Expert Comment

by:Rob Henson
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 33

Accepted Solution

by:
Rob Henson earned 500 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".

Try this formula instead:

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

Thanks
Rob H
0
 
LVL 33

Assisted Solution

by:Rob Henson
Rob Henson earned 500 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 30

Author Comment

by:Randy Downs
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

by:Randy Downs
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

by:Randy Downs
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))

Open in new window

0

Featured Post

SuperAntiSpyware Licenses Discounted by 25% !

Exclusive offer to Experts Exchange Members!
Buy SuperAntiSpyware License(s) from us and save 25% on the regular purchase price.
- Includes Full SuperAntiSpyware Vendor Support Entitlements
- Your Subscription does not begin until you activate your license
- Buy for your friends

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Email accounts in Outlook owa 4 43
Outlook owa - How to add an email to an existing conversation 5 17
How to update GAL in O365? 4 32
sort time order 10 45
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question