summing multiple criteria between date range formula question

Hello group!

I was curious if anyone knew a more efficient way to either shorten or to take the following data/formula to sum the data between two dates as reflected in another formula below this one?:

Please Login or Register  to view this content.

This formula represents one criteria to sum between two dates, but I need it to reflect all of the criteria above without making a mega long formula if possible, etc:

Please Login or Register  to view this content.

Thanks for any suggestions!

Thanks mcmahobt for the formula suggestion. Any way I could use something like that to sum between two dates as reflected in the 2nd formula?

mcmahobt-I need it for every sumproduct within the summation between the two date ranges. Sorry for my belated reply!

Thanks mcmahobt I'll test it out

mcmahobt-I attached a sample workbook with a shorter formula attempt to test the formula. I'm getting an error, but I might have misunderstood your formula.

Ideally what I need the formula to do is calculate hours worked within a weeks date range. So in the sample I've set it for the week of May 3rd thru the 9th; the dates being set from the "Payroll" sheet. In my example for John Doe he has one 4hr comp time off in that week. So the formula would recognize that 4hrs and would provide me the end result of 36hrs, which they worked that week. For Jane Smith the formula when applied would give me 48 (due to the fact that her scheduled days off (DO) were not added in the sample for that same date range. Hope this makes sense! Thanks again for taking the time to look at this!

mcmahobt-My actual workbook is a yearly schedule with tabs for each month (January through December). The sample just shows a very small section of what the schedule would like for two employees for the month of May 2015. Our bi-weekly payroll ends on a Saturdays, which the cutoff is reflected by the "X" in row 2. The schedule has many options that an employee can take, throughout their given work week; i.e. compensatory time off, vacation time, personal leave, sick time, etc (all at varying hours). The first formula (the longest one) demonstrates all of the types of symbols that an employee could have potentially show up in any given week on the work schedule. My sample only demonstrated two types (one for each employee) during the week of 5/3 to 5/9. The Payroll sheet does a lot more than what this sample payroll sheet does, but both break down each week that I need to keep track of hours worked by an employee; especially important now for part-time employees so that they don't exceed maximum hours allowed. In the sample workbook on the May sheet in cell AJ4 I have text 5/3-5/9. I would have additional dates to the right of that cell like 5/10-5/16 and so on for those weeks in May. Under each of the text dates I need a formula to look at that particular employee's schedule row between that given date range and then count any empty cells as 8hrs worked, plus it must recognize the other symbols (i.e. 4V, 4C, 4c▼, etc). So if an employee had a 4V in that given week as the only time off (minus their normally scheduled days off [DO]), the formula would recognize the empty cells as 8hrs each and the 4V as the employee worked 4 hrs only of their 8 hr shift and the result would be 36. If an employee had taken 1.5 hrs of personal leave time instead and had worked the rest of the week; the formula would give me the result of 38.5 for that week that the employee worked. Let me know if this explanation helped or not. Thanks again!

mcmahobt-I really appreciate your efforts, but I don't think that will do what I need. Thanks for taking the time to help though. Your suggestions are very helpful!