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
Bookmarks