Sum weekly columns to a monthly total with criteria.

I am trying to sum up data from columns that are based on a week ending date and total them into a monthly total. There is also additional criteria needed for the summations. I will try to describe the steps below... (needs to work in Excel 2003 or 2007).

There are 3 tabs involved. A set up tab, a weekly input tab, and a monthly totals tab. To simplify I have left off the setup tab and placed the layout of the other two tabs onto one sheet for easier viewing and have reduced the number of columns for the weekly & monthly views.

1) in the original workbook a date is entered. This is used to calculate the column headings on the "monthly" and "weekly" tabs. So the number of weekly tabs for the first reported month could vary from 1-5 columns. It will not always be the same. (I haven't included those calculations as I have them working just fine).

2) Personnel names & rates are populated and work hours are added on a weekly basis to the weekly tab.

What I am trying to do is sum up the monthly costs by department (dept) by gathering the data of each person on the "weekly" tab and roll it up to the appropriate monthly value. The value is based according to the department (dept), rate, and hours they put in each week. The problem is the columns for each month are not static because the column headings are caluclated. Example follows:

Sue & Tom both work for HR. In May Sue worked 8 hours & Tom worked 9 hours. They both have the same rate of $10. Therefore the total for May for HR should be $170.


Any help would be greatly appreciated.

Thanks.

Based on your sample:

B2:
=SUMPRODUCT(($H$1:$O$1-DAY($H$1:$O$1)+1=B$1)*($G$2:$G$7=$A2),$H$2:$O$7*$F$2:$F$7)
copied across matrix B2:C4