Query and sum based on multiple parameters

Hi all,

I have a list of jobs - who's doing the work, an estimated duration and when it will be completed - and I'm trying to find an equation that will be able to query the list and take a sum for each person by week and day.

For example, say I schedule John for a three hour job on Tuesday of week 44 with an estimated duration of three hours. I could write a sumif equation to look in an array for 'John' and return the duration, but that doesn't filter out the jobs I have him scheduled for on Wednesday of week 45. Index and Match functions haven't worked because I have mulitple jobs in a day and I'm after a sum. PivotTables haven't been all that helpful either...

Any ideas will be greatly appriciated! Thank you

Try using SUMPRODUCT, e.g.

=SUMPRODUCT((A2:A100="John")+0,(B2:B100=45)+0,(C2:C100="Wednesday")+0,D2:D100)

where column D contains the hours

Maybe an easier way (if you know of a method) is using a PivotTable. Trouble is I pull these numbers from an outside source, the dates come back in a form 09/30/2007 11:37 AM, then if another job on the same day with a different time the PivotTable will seperate those just because of having different times. I can group all jobs that fall on a given day but with 400+ a week it'd get time consuming.

This make sense? I hope so.. it's having the time associated with the day thats killing me. Thanks for your help

I think I got it - I added another column and used =text(B1,"dddd"); where before I was saying =(B1) and tried a custom format to show whatever day of the week it was. Doing it that way I'd have like 8 jobs labeled under 'Monday' for week 40 just because they started at different times - having that column as text seems to work though.

Thanks for you time and help!