+ Reply to Thread
Results 1 to 3 of 3

Sum values between a range of dates

  1. #1
    Registered User
    Join Date
    11-02-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    32

    Sum values between a range of dates

    Timesheet Example.xlsx

    I'm working with a timesheet workbook where employees enter their hours biweekly. However due to an oddity between two departments, the way that the hours are recorded and the way that HR pays the employees the weeks don't line up. For example hours are recorded for management purposes from 10/29-11/9 but the pay period is calculated as hours from 11/1-11/15 (it's really dumb, I know).

    So I basically want to use SUMIFS except as far as I can tell you can't use an operator like >= with are variable with SUMIFS.

    So I want a way to sum the numbers in one column (the total hours worked for a day) that fall within a range of another column (begin date of a period and end date of a period).

    I feel like I've gotten really close with a couple of tries but I keep coming up short. Any advice?
    Last edited by jmulqueen; 11-21-2012 at 01:28 PM.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Sum values between a range of dates

    You will need to move your existing dates down one row in column M, so that you can put the start-date of the range in M2 (i.e. 15th Oct 2012), then you can use this formula in N3:

    =SUMIFS(G:G,B:B,"<="&M3,B:B,">"&M2)

    and then just copy this down.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    11-02-2012
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    32

    Re: Sum values between a range of dates

    That's perfect thanks. I didn't realize you could use an ampersand like that with SUMIFS. That's great to know. Thanks so much.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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