+ Reply to Thread
Results 1 to 10 of 10

Excel 2007 : copy and sum specified cells to another worksheet if date cell is between date range

  1. #1
    Registered User
    Join Date
    04-10-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    5

    copy and sum specified cells to another worksheet if date cell is between date range

    Hi,

    I am quite a newbe so please be patient, thanks.

    I currently have a worksheet of data for each different year since 2008. I need to copy specific cells if the date in that row (cell is in same column for each worksheet) falls within the date range I specify to a new worksheet (named totals). Then I will need to sum some of the columns in the TOTALS worksheet.

    This may sound childish to some but it is colossal for me.

    Thanks

    Joe

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: copy and sum specified cells to another worksheet if date cell is between date range

    Hi Joe,

    Welcome to the forum.

    Please post a sample workbook. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    04-10-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: copy and sum specified cells to another worksheet if date cell is between date range

    Hi DILIPandey,

    Thanks for the interest shown. I am trying to attach the file now.

    The date I want to excel to consider for this operation is the one displayed in column J. If this date falls within a range I specify (say 01/01/2012 to 31/03/2012) I wish that the data in columns O,P,Q, & R is copied to the new worksheet (TOTALS). I will then use autosum for the totals (this bit at least I Know ).

    I appreciate your patience.

    Joe
    Attached Files Attached Files

  4. #4
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: copy and sum specified cells to another worksheet if date cell is between date range

    Hi Joe,

    I specify (say 01/01/2012 to 31/03/2012) I wish that the data in columns O,P,Q, & R is copied to the new worksheet (TOTALS).
    Where do you want to specify these dates ? In this worksheet or in new worksheet (Totals) ?
    I guess this can be achieved using a formula which will be there in worksheet (totals) . . please confirm, so that I can show you a sample. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  5. #5
    Registered User
    Join Date
    04-10-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: copy and sum specified cells to another worksheet if date cell is between date range

    If I can manipulate the date range in the formula in the 'Totals' worksheet, I think it will do the trick for me. Unless you can guide me to a simpler solution.
    I thank you once more for your kind assistance.

  6. #6
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: copy and sum specified cells to another worksheet if date cell is between date range

    Hi Joe,

    See the attached file where I have used the date ranges to get the sum of respective columns in sheet "Totals" .. the cells which falls under the criteria has been highlighted in yellow in sheet 1.. thanks.

    Regards,
    DILIPandey

    <click on below 'star' if this helps>
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    04-10-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    5

    Thumbs up Re: copy and sum specified cells to another worksheet if date cell is between date range

    Dear DiliPandy,

    You are a wizard mate. I am still trying to work out how you did that. If you don't mind I will come back in a couple of days' time to enquire what the end of the formula means. But for now I will try to implement it in my worksheets.

    I really appreciate.

    Regards,

    Joe

  8. #8
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    Re: copy and sum specified cells to another worksheet if date cell is between date range

    np... you are always welcome..

    Regards,
    DILIPandey

    <click on below 'star' if this helps>

  9. #9
    Registered User
    Join Date
    04-10-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: copy and sum specified cells to another worksheet if date cell is between date range

    Good morning Dilipandy,

    It's me again. I am trying to change the date column reference from sheet 1, from C to column J. when I do this the result is Value!!
    this is my amended formula in Totals C2: =SUM(IF((Sheet1!$J$2:$J$66>=TOTALS!$A$1)*(Sheet1!$J$2:$J$66<=TOTALS!$B$1),Sheet1!O$2:O$66,""))
    what am I doing wrong??
    Please bear with me.

    Joe

  10. #10
    Forum Guru
    Join Date
    05-24-2011
    Location
    Kuwait
    MS-Off Ver
    Office 365
    Posts
    2,199

    Re: copy and sum specified cells to another worksheet if date cell is between date range

    Joe,

    That is an Array Formula, so must be entered with CTRL+SHIFT+ENTER, rather than just ENTER.

    You could also use this formula with just ENTER.

    Please Login or Register  to view this content.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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