+ Reply to Thread
Results 1 to 11 of 11

Date Range Sum

  1. #1
    Registered User
    Join Date
    07-26-2014
    Location
    india
    MS-Off Ver
    2010
    Posts
    66

    Date Range Sum

    Hi

    I have attached one file containing 2 sheets
    in second sheet I am trying to calculate date Range wise Sum
    but date wise values are in first sheet
    #Value! error is coming


    Atul
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Date Range Sum

    Here is one possible solution for you. paste into Sheet B G10

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Date Range Sum

    Try this..

    This will also...
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Don't forget to click *
    Last edited by JBeaucaire; 08-29-2014 at 01:11 PM.

  4. #4
    Registered User
    Join Date
    07-26-2014
    Location
    india
    MS-Off Ver
    2010
    Posts
    66

    Re: Date Range Sum

    Thanks for Reply @ Glenn Kennedy and Vikas_gautam

    I think there is confussion for understanding Query

    I have given date Range From Date in G8 and To date in H8
    when I am changing from date then ouput is not coming correct
    because in your formula you have not given reference for cell G8

    Atul

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Date Range Sum

    I assumed that you always wanted to start from the first of the month. Leave it with me...

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Date Range Sum

    OK. Sorted out (I hope). Try this.
    Attached Files Attached Files

  7. #7
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Date Range Sum

    Try this.. in G10 Sheet B
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Don't forget to click *, again
    Last edited by JBeaucaire; 08-29-2014 at 01:10 PM.

  8. #8
    Registered User
    Join Date
    07-26-2014
    Location
    india
    MS-Off Ver
    2010
    Posts
    66

    Re: Date Range Sum

    Yes Now Value is comming correct

    Thanks to Vikas and Glenn

    Dear Glenn why you took Reference of Column no. 5 that I didnt understood

    Atul

  9. #9
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Date Range Sum

    Your data begins in column F - 6 as a number. The match formula returns a 1 for the 1st August in column F. If you add 1 to 6 it would start the sum range 1 column to the right. So, I subtracted 1. To be honest, Vikas' solution is simpler. I'd use it, if I were you.

  10. #10
    Registered User
    Join Date
    07-26-2014
    Location
    india
    MS-Off Ver
    2010
    Posts
    66

    Re: Date Range Sum

    Exact

    I am using Vikas's Solution only because it is more easy and simple
    But I want to know why you took reference of Column no. thats why I asked you.

    I am happy that you declared that Vikas's solution is perfect

    Thanks Vikas

  11. #11
    Forum Expert Vikas_Gautam's Avatar
    Join Date
    06-04-2013
    Location
    Ludhiana,Punjab, India
    MS-Off Ver
    Excel 2013
    Posts
    1,850

    Re: Date Range Sum

    Hi Atul..

    Here is the explanation...

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    =Offset(Base_Reference, Row_Offset, Column__________Offset, Height , Width )

    =Offset( A!F7 , 1 , MATCH(G8,A!F7:AJ7,0)-1 , 7 , MATCH(H8,A!F7:AJ7,0))

    As you can see above, I am using Column Offset to set your start date and Width to set your end date...

    I have deducted 1 because Column offset is exclusive of base reference and width is inclusive of Base Reference...

    Use formula auditing to understand it more..
    and use excel inbuilt help to know more about the offset function..

    Don't forget to click *
    Last edited by JBeaucaire; 08-29-2014 at 12:22 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Highlight a date range if today's date falls within that range
    By sdarnell in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-01-2014, 10:03 AM
  2. VBA to enter date range based on date range in above cell
    By Pierce Quality in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-19-2013, 08:45 AM
  3. Search date range +/- 7 days and return date that falls within range
    By tlafferty in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-13-2013, 03:26 PM
  4. [SOLVED] Calculate how many days in one date range fall into a second date range
    By globalpontoon in forum Excel General
    Replies: 2
    Last Post: 05-08-2012, 03:02 PM
  5. Replies: 4
    Last Post: 03-18-2011, 07:02 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1