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

1. ## 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.

James

2. ## 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. ## 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. ## 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)

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