+ Reply to Thread
Results 1 to 4 of 4

Excel 2007 : SUMIFS forumla into something Excel 03 can handle - work distribution requirement!

  1. #1
    Registered User
    Join Date
    03-25-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    6

    SUMIFS forumla into something Excel 03 can handle - work distribution requirement!

    Hi all,

    I've built a sumifs formula in Excel 2007 that works perfectly. Unfortunately many of our employees who the workbook is sent to are still operating on Excel 2003. As you all probably know; SUMIFS are not handled in 2003 and you recieve a #NAME error.

    I have a table with employee name in column A and weeks commencing in row 3 (starting at column B). The idea is to sum the amount of hours holiday each employee is taking in a particular week.

    In a second sheet i have my raw data source which is a drag from our central systems and is broken down with daily holiday entries i.e. Column A = Employee, Column B = date, Column C = hours holiday on given date.

    The SUMIFS forumula which i've succesfully used is (split into 3 parts for clarity):

    A) =SUMIF(Sheet2!A6:A6000,Sheet1!A6,Sheet2!C$:C6000)

    B) -SUMIFS(Sheet2!C6:C6000,Sheet2!B6:B6000,"<"&'Sheet1'!B3,Sheet2!A6:A6000,Sheet1!A6)

    C) -SUMIFS(Sheet2!C6:C6000,Sheet2!B6:B6000,">="'&'Sheet1'!B$3,Sheet2!A6:A6000,Sheet1!A6)

    In English:
    A) Sum of all of Employee A's holiday in the raw data.
    B) Minus all holiday with a date earlier than start of particular week commencing date.
    C) Minus all holiday with a date later than end of particular week i.e. anything with a date equal or greater to the week commencing date of the following week (in the next cell on row 3).

    This leaves me with all holiday for employee A within the week with week commencing date of B3.

    Now my question (extremely long winded one) is what formula can i use to do the same thing that is handled by Excel 2003, and is simple enough to sit in one cell? I have read a lot about SUMPRODUCT and array forumulas but have thus far had no luck in making them work possibly due to not being able to select my desired date criteria; i'm not sure....maybe it's a syntax thing.

    Any help ASAP would be extremely appreciated, i need to get this distributed within the next 4 hours.

    Thanks for all your help.

    James

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: SUMIFS forumla into something Excel 03 can handle - work distribution requirement

    This
    =-SUMIFS(Sheet2!C6:C6000,Sheet2!B6:B6000,"<"&'Sheet1'!B3,Sheet2!A6:A6000,Sheet1!A6)

    in Sumproduct terms...

    =-SUMPRODUCT(--(Sheet2!A6:A6000=Sheet1!A6),--(Sheet2!B6:B6000<Sheet1!B3),Sheet2!C6:C6000)

  3. #3
    Registered User
    Join Date
    03-25-2009
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    6

    Re: SUMIFS forumla into something Excel 03 can handle - work distribution requirement

    Thanks for the help but unfortunately this is giving me an #N/A error. Do you have any idea why this would be the case?

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: SUMIFS forumla into something Excel 03 can handle - work distribution requirement

    Given the SUMIFS work I would presume the issue relates to either of or both the following ranges containing #N/A error values

    Sheet2!A6:A6000
    Sheet2!B6:B6000

    You could convert to a CSE array to ignore the errors but it would be far better to remove the errors at source (ie correct Sheet2)

+ 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