+ Reply to Thread
Results 1 to 7 of 7

2 range sum formulas based on one date

  1. #1
    Registered User
    Join Date
    02-01-2010
    Location
    Colorado
    MS-Off Ver
    Excel 2007
    Posts
    13

    2 range sum formulas based on one date

    In the attached spreadsheet example, is there a way to:

    Reference the month and day ONLY of Clmn A:

    Then have it auto create a sum formula . . .

    I put the days of the year across (had to delect some clmns due upload size. . .), and at the end of that range per row, I need:

    The next clmn to be SUM of Jan-1 to day before Date of Hire.
    then
    The next clmn to be Sum of Date of Hire to Dec-31.

    This is so confusing to manually select like I did in the example.

    Is there a way to get this to automatically select the range based on the date? (And then these sums will be referenced in another clmn as well. . .)

    Any brave souls out there to try this?

    I would really appreciate it!!!

    Thank You!

    - Daphne
    Attached Files Attached Files
    Last edited by Daphne; 02-27-2010 at 03:55 PM. Reason: I've been helped by great gurus!

  2. #2
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: 2 range sum formulas based on one date

    Maybe like this?
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    02-01-2010
    Location
    Colorado
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: 2 range sum formulas based on one date

    I can't wait to try this!! THANK YOU for your response!!!!!. . . It looks like it will do it. . . I can't wait to figure out what all of it means and reference all the correct cells in the orginal worksheet!!!! This web site is addicting, better than working puzzles!

    Sincerely THANK YOU! I will post back shortly after I work it in the ss!!

    - Daphne

  4. #4
    Registered User
    Join Date
    02-01-2010
    Location
    Colorado
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: 2 range sum formulas based on one date

    You are GREAT!!! How do you know this stuff!!!!!??????

    This works on all dates except the December Dates??? I figure it has to do with being able to sum up to 255 (and I have 365 days of) columns, but why is it working for October and November???

    So I have to work that out. Any suggestions? I'll be working on it all night, checking in every few!!!!!!!
    !!

    Sincerly!
    Thanks!
    Daphne

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: 2 range sum formulas based on one date

    Daphne,

    Late to the party but I'm not sure you can't use a standard SUMIF here, eg:

    Please Login or Register  to view this content.
    Adjust ranges to suit of course.

    The above is based on the premise that for the person in Row 6 of your file (start date 27-Mar-06) - if they have vacation booked on 15-Mar-10 this goes in the first sum rather than the second
    Last edited by DonkeyOte; 02-25-2010 at 04:32 AM. Reason: added ex.

  6. #6
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: 2 range sum formulas based on one date

    Daphne,

    You're right about the 255 column limit and the need to account for 365 days, i didn't think that through, it shouldn't work for october, november,december. Don's suggestion is (as usual) the simpler approach and works. This also answers your other question:" How do you know this stuff!!!!!??????" I learned this over here on the forum (and i don't mean making stupid mistakes like this).

  7. #7
    Registered User
    Join Date
    02-01-2010
    Location
    Colorado
    MS-Off Ver
    Excel 2007
    Posts
    13

    Re: 2 range sum formulas based on one date

    You both are "Gods" to me!!!! Don's example is working, I can't make it not work!!! Trust me, I'm good at making formulas not work!!! But WHER, I learned a lot from trying yours! I'm into this website! I'm a stay at home mom now, and I miss the 8 - 5++! That's why I'm doing this spreadsheet for a prior manager. It's been a challenge! Now my challenge is going to be regularly viewing the website, learning, and hopefully helping others!!!! I wish that everyone learns from this website like I am, not just taking advantage of the answers and still not knowing what they did!

    Don, thank you for joining the party late!!! and I like your closing reading references! I did! Does the beer go to you?

    Sincerely to you both!!!!
    Cheers!

    - Daphne

+ 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