Array SUM Formula for SUM with multiple criteria and a date range (Using Excel 2003)
I have a list of timesheet data for various employees where I am trying to sum the number of hours worked for a particular Sunday week commencing date by employee. I had this working when it was looking at one month in isolation but I have been asked to change this so that when the week covers two months that the “this weeks” summary of hours worked still takes into account the full 7 days not just the part week that applies to the current month
Eg My data is structure like this
Employee Code = an alpha code that is unique per employee
Charge Flag = can be Y or N
Hours Worked = number of hours from the timesheet
Doc_MM = the month number that the timesheet relates to ie 4 = April
Doc_DD = the day number that the timesheet relates to
So for week commencing Sunday the 27th April 2008 I need to sum the number of hours worked by employee for timesheets with a chargeable flag = Y for the following doc_MM and doc_dd combinations
Remembering that the raw data for this example would contain all the timesheets for the month of April and May.
This spreadsheet is updated by a user each week and the focus W/C date will change each week
Any help would be greatly appreciate
why don't you make an extra column Doc_WK where you have the weeknumber of the date (using WEEKNUM-function) and summarize the values by that column?
Conditional sum array
Below is an array I use to sum data from diffferent tabs using variance criteria. I hope this helps.
=SUM(IF(('(7.2b) Raw Lawson Data'!$A$6:$A$6000=9291)*('(7.2b) Raw Lawson Data'!$E$6:$E$6000=$A14),'(7.2b) Raw Lawson Data'!$D$6:$D$6000,0))+SUM(IF(('(7.2b) Raw Lawson Data'!$A$6:$A$6000=9211)*('(7.2b) Raw Lawson Data'!$E$6:$E$6000=$A14),'(7.2b) Raw Lawson Data'!$D$6:$D$6000,0))
The weeknum function will probably cause me issue in the Dec/Jan cross over period.
However thank-you for your post as your suggestion gave me the idea to use vlookup to just add some text next to the dates for that week and use that as one of the criteria
Sometimes you just need a new set of eye's to look at something
I'm glad I could give you a kick in a new direction If you need any help in summarizing the data, just post in this thread and I'll try to help you as I can.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1