+ Reply to Thread
Results 1 to 5 of 5

SUMIFS or SUMPRODUCT with multiple criteria including date ranges

  1. #1
    Registered User
    Join Date
    03-23-2011
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    3

    Question SUMIFS or SUMPRODUCT with multiple criteria including date ranges

    I have been fumbling with this one for awhile and I think it is time to reach out!

    I have three columns and I would like to total the HOURS column for each individual listed in the NAME column, based on a date range (ie. between Apr 1 to Apr 6).

    NAME HRS DATE
    Bob 2 2-Apr-13
    Bob 1 4-Apr-13
    Sam 2 2-Apr-13
    Bob 4 8-Apr-13

    I have tried SUMIFS and SUMPRODUCT, but I'm falling short. The date range is particularly challenging. Is this doable?

    Thanks!

  2. #2
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: SUMIFS or SUMPRODUCT with multiple criteria including date ranges

    Try this...
    Attached Files Attached Files

  3. #3
    Valued Forum Contributor Harribone's Avatar
    Join Date
    02-24-2013
    Location
    Midlands, UK
    MS-Off Ver
    Excel 2019/365
    Posts
    551

    Re: SUMIFS or SUMPRODUCT with multiple criteria including date ranges

    Assumin:

    Col A is NAME
    Col B is HOURS
    Col C is DATE

    F2 enter Name of choice
    F3 enter start date
    F4 enter end date

    F5 type - =SUMIFS(B:B,A:A,F1,C:C,">="&F2,C:C,"<="&F3)

    Don't know if SUMIFS works in 2003 so may need sumproduct solution (don't do these myself - never learnt how to use!)

    EDIT: Beaten to it! Above solution from djapigo will do the job.
    Say thanks, click *

  4. #4
    Valued Forum Contributor
    Join Date
    09-10-2012
    Location
    Los Angeles, CA
    MS-Off Ver
    Excel 2010
    Posts
    929

    Re: SUMIFS or SUMPRODUCT with multiple criteria including date ranges

    Harribone,

    Your formula will work for Excel 2007 or higher, but I figured baxcat only had Excel 2003... the SUMIFS formulas are pretty new and nice formulas...

  5. #5
    Registered User
    Join Date
    03-23-2011
    Location
    Canada
    MS-Off Ver
    Excel 2003
    Posts
    3

    Wink Re: SUMIFS or SUMPRODUCT with multiple criteria including date ranges

    Thank you Harribone & djapigo. I neglected to change my stats to show that we have updated to Excel 2010 at work. Both solutions worked perfectly. Thank you very much. I can apply this in many ways.

+ 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