+ Reply to Thread
Results 1 to 9 of 9

sum week to date values based on todays date

  1. 08-16-2013, 12:38 AM #1
    nicko54
    nicko54 is offline
    Forum Contributor
    Join Date
    06-30-2008
    Location
    london
    Posts
    108

    sum week to date values based on todays date

    Hi all,

    I am trying to sum up the week to date of values in cells.

    I need to find a way to lookup todays date and sum values in a column from the start of the week to todays date.

    So if it is Friday today then the week to date value is the sum of values in say column b from Monday to Friday.

    I have attached an example to give a bit more of a guide.

    Any ideas?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 08-16-2013, 12:50 AM #2
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: sum week to date values based on todays date

    I did this just for Mike...
    =SUMIFS($B$4:$B$19,$A$4:$A$19,"<="&TODAY(),$A$4:$A$19,">="&TODAY()-WEEKDAY(A19,2)+1)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  3. 08-16-2013, 12:54 AM #3
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: sum week to date values based on todays date

    hi nicko54. it's good to put in what version of Excel are you using, so that we can provide you with appropriate formulas. seeing that you are using xlsx, i assume you can use SUMIFS. and what would be the answer you require? sum of an individual person or all? for individual, use FDibbins' formula

    for all,
    =SUMPRODUCT((B4:D19)*(A4:A19>A1-WEEKDAY(A1,2))*(A4:A19<=A1))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  4. 08-16-2013, 01:03 AM #4
    nicko54
    nicko54 is offline
    Forum Contributor
    Join Date
    06-30-2008
    Location
    london
    Posts
    108

    Re: sum week to date values based on todays date

    Brilliant! I wanted it for the individual. This works great. thanks heaps.
    Reply With QuoteRegister To Reply
  5. 08-16-2013, 01:09 AM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: sum week to date values based on todays date

    Just out of curiosity, which 1 worked for you?
    Reply With QuoteRegister To Reply
  6. 08-16-2013, 01:09 AM #6
    ajryan88
    ajryan88 is offline
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: sum week to date values based on todays date

    Have a look at the orange cells:

    EDIT: Sorry, I didn't realise this thread was answered in the time it took me to develop the formula and reply to your thread
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  7. 08-16-2013, 01:16 AM #7
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: sum week to date values based on todays date

    @ ajryan, no apology needed Im not surprised it took you a while to put that together - good job

    From a learning point of view, see how your formula compares to those above
    Reply With QuoteRegister To Reply
  8. 08-16-2013, 01:17 AM #8
    ajryan88
    ajryan88 is offline
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: sum week to date values based on todays date

    Thanks FDibbins
    Reply With QuoteRegister To Reply
  9. 08-19-2013, 07:48 PM #9
    nicko54
    nicko54 is offline
    Forum Contributor
    Join Date
    06-30-2008
    Location
    london
    Posts
    108

    Re: sum week to date values based on todays date

    Hi guys,

    I used FDibbins formula
    HTML Code: 
    but I have just realised it is summing 5 days instead of the week to date from Monday.

    Is there an easy way to change this?
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. calculate date based on todays date
    By dazlia in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-06-2012, 07:24 AM
  2. Using VB to find a range based on todays date and todays date +30
    By Steve_al in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-13-2009, 09:31 AM
  3. Filling cells with 3 different colors based on date in cell and todays date
    By chinookcrew in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-23-2009, 07:36 AM
  4. week start date and end date based on week number
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-02-2009, 12:39 PM
  5. Finding the Monday date based on a different date in same week
    By dandiehl in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-11-2006, 01:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1