Multiple SUMIFS?

[Double99]

This is my first forum entry...

I was wondering if anyone can solve my problem.

I have a list of about 2,500 entries containing information of all my works leave requests for 2009. This list contains more than one entry per employee, as it shows all their individual requests for different weeks.

The list is compiled pulling through data from other worksheets. It looks like this...

Sheet Name: MASTER

Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column E = Week ending
Column F to L = Days of the week
Column M to O = Request Type
Column P = Number of days requested for the week

I then have another sheet (Called REQUESTS) with all the employee names (1 entry for each employee), ID numbers and total requests for the year. This looks like this...

Column A = Line Managers Surname
Column B = Employees Surname
Column C = Employees First Name
Column D = Employees ID Number
Column P = Number of days requested for the year
Column Q = Number of days requested for the summer

I know how to locate and add up all the requests for each individual for the whole year (Column P) from the MASTER sheet to the REQUESTS sheet by using the flowing formula for each employee.

=SUMIF(MASTER!$D:$D,D2,MASTER!$P:$P)

But I don't know how to ask Excel to find and add up all the total requests for only the summer period (Column Q)

What I want Excel to do is this...


1. Find all the ID Number entries on the MASTER Sheet (MASTER!$D:$D) that are equal to...
2. The ID number in cell D2 (D2 on the REQUESTS Sheet)
3. Only count up the number of days requested if the date in Column E on the MASTER Sheet that falls between 01/07/09 and 30/09/09 in the week ending column. (MASTER!$E:$E (DATE=<01/01/09)*(DATE=<30/09/09)) *Not sure if that formula is right...
4. If so take the number of requests in column P (MASTER!$P:$P) and add it to the total in (in Colunm Q on the REQUESTS Sheet)

If formula was correct it would only only display the number of days requested (in Colunm Q on the REQUESTS Sheet) which were made during the summer period for each employee.


Essentialy it the same formula as =SUMIF(MASTER!$D:$D,D2,MASTER!$P:$P), but with one extra criteria attatched, that being the date range.

Does any know how to to add in the date as an extra critreia into the formula,?

Do I need to use SUMPRODUCT or SUMIFS. I don't know??

Hope this all makes sense...

Many Thanks

[NBVC]

Try

Please Login or Register  to view this content.

adjust ranges to suit...


Note: Cannot use whole column references like $D:$D in XL2003 or earlier

[Double99]

Thankyou so much!!